工作SQL总结

1.

 SELECT SUM(IFNULL(mc.coupon_nominal_value,0)*IFNULL(mc.coupon_count,0)) AS couponCount, 
		SUM( IFNULL(mc.coupon_count,0)) AS couponNum , 
		m.merchant_name AS merchantName, 
		mc.merchant_id AS merchantId,
		mc.merchant_coupon_id,
		mcs.merchant_coupon_specification_name AS mcsName
		FROM backend.merchant_coupon mc 
		JOIN backend.merchant m ON m.merchant_id=mc.merchant_id 
		JOIN backend.merchant_coupon_specification mcs ON mcs.merchant_coupon_specification_id=mc.coupon_specification_id 
		WHERE mc.is_deleted=0 
		AND mc.coupon_nominal_value>0 AND mc.coupon_count>0 
		GROUP BY merchantId,mcsName
		
		
	2.	SELECT COUNT(DISTINCT mc.coupon_specification_id,mc.merchant_id) FROM backend.merchant_coupon mc 
                 WHERE mc.is_deleted=0 
           AND mc.coupon_nominal_value>0 AND mc.coupon_count>0 

SELECT IFNULL(mc.coupon_nominal_value,0)*SUM(mca.coupon_count), ds.distribution_station_name AS distributeName,SUM(mca.coupon_count) AS couponNum,mc.merchant_coupon_name AS couponName,mcs.merchant_coupon_specification_name AS couponSpecificationName
FROM tms.distribution_station ds
JOIN backend.merchant_coupon_assignment mca ON ds.distribution_station_id=mca.distribution_station_id
JOIN backend.merchant_coupon mc ON mca.merchant_coupon_id=mc.merchant_coupon_id
JOIN backend.merchant_coupon_specification mcs ON mcs.merchant_coupon_specification_id=mc.coupon_specification_id
GROUP BY mca.distribution_station_id,mca.merchant_coupon_id