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