SELECT c.aid activity_id, DATE_FORMAT(c.createdate, '%Y%m%d') statistics_date,
COUNT(c.id) total,
SUM(
CASE
WHEN (
(
IFNULL(c.points, 0) + IFNULL(c.money, 0) + IFNULL(c.cashcoupon, 0)
) / a.price
) * 100 >= 100
THEN 1 //这里是指满足条件就为1，不满足条件就为0，然后sum()会统计总数
ELSE 0
END
) finish,
SUM(
CASE
WHEN (
(
IFNULL(c.points, 0) + IFNULL(c.money, 0) + IFNULL(c.cashcoupon, 0)
) / a.price
) * 100 BETWEEN 50
AND 99
THEN 1
ELSE 0
END
) more_half,
SUM(
CASE
WHEN (
(
IFNULL(c.points, 0) + IFNULL(c.money, 0) + IFNULL(c.cashcoupon, 0)
) / a.price
) * 100 BETWEEN 30
AND 49
THEN 1
ELSE 0
END
) one_third,
SUM(
CASE
WHEN (
(
IFNULL(c.points, 0) + IFNULL(c.money, 0) + IFNULL(c.cashcoupon, 0)
) / a.price
) * 100 BETWEEN 10
AND 29
THEN 1
ELSE 0
END
) deci,
SUM(
CASE
WHEN (
(
IFNULL(c.points, 0) + IFNULL(c.money, 0) + IFNULL(c.cashcoupon, 0)
) / a.price
) * 100 BETWEEN 1
AND 9
THEN 1
ELSE 0
END
) percentile,
SUM(
CASE
WHEN (
(
IFNULL(c.points, 0) + IFNULL(c.money, 0) + IFNULL(c.cashcoupon, 0)
) / a.price
) * 100 BETWEEN 0.1
AND 0.9
THEN 1
ELSE 0
END
) zero_one,
SUM(
CASE
WHEN (
(
IFNULL(c.points, 0) + IFNULL(c.money, 0) + IFNULL(c.cashcoupon, 0)
) / a.price
) * 100 = 0
THEN 1
ELSE 0
END
) zero
FROM
crowds AS c
LEFT JOIN activities AS a
ON c.`id` = a.`id`
WHERE c.aid = 1
GROUP BY statistics_date；
654
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
