sql 排坑

case when then else end   语句

接近于java 中的 switch (条件){case 值: 语句 break;}  

case when 条件 then 值 else 条件不成立时的值 end 用于结尾 

left join   on  语句

on  之后 不仅仅只接一个   x=x  而是可以 用and 连接多个 

最后粘上一段 排坑之前的 代码

--  已知 活动ID
--  活动商品ID , 活动商品名称
SELECT cc.id ,cc.`name`
FROM carlife_activity_commodity cac
LEFT JOIN carlife_commodity cc ON cc.id = cac.carlife_commodity_id
WHERE cac.carlife_activity_id = '19'
-- 已知 活动ID 和 商品ID
-- 购买人数,使用人数
SELECT COUNT(CASE WHEN co.`status` = '1' THEN co.id END) payNum, 
				COUNT(CASE WHEN cacwo.type = '1' THEN cacwo.id END) useNum
FROM carlife_order co
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE co.carlife_commodity_id ='1' AND co.carlife_activity_id = '17'

和排坑之后的代码

SELECT DISTINCT cc.id commodityId, cc.`name` commodityName , COUNT(CASE WHEN co.`status` ='1' THEN co.id  END) payNum,
COUNT( CASE WHEN cacwo.type = '1' THEN cacwo.id  END) useNum
FROM carlife_activity_commodity cac
LEFT JOIN carlife_commodity cc  ON cac.carlife_commodity_id = cc.id
LEFT JOIN carlife_order co ON co.carlife_commodity_id = cc.id AND cac.carlife_activity_id = co.carlife_activity_id
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE cac.carlife_activity_id = #{activityId,jdbcType=VARCHAR}
GROUP BY cc.id
ORDER BY cc.id DESC