81,091
社区成员
发帖
与我相关
我的任务
分享
SELECT
sum(CASE WHEN t.num = t.total THEN t.total ELSE 0 END) AS success ,
sum(CASE WHEN t.num <> t.total THEN t.total ELSE 0 END) AS defeated
FROM
(
SELECT
sum(
CASE
WHEN STATUS = 'DELIVERD' THEN
1
ELSE
0
END
) AS num,
count(1) as total
FROM
sms_msgid_log_01
GROUP BY
dest_teminal_id ,
msgid1
) t
SELECT
sum(CASE WHEN t.num = 5 THEN 5 ELSE 0 END) AS success ,
sum(CASE WHEN t.num <> 5 THEN 5 ELSE 0 END) AS defeated
FROM
(
SELECT
sum(
CASE
WHEN STATUS = 'DELIVERD' THEN
1
ELSE
0
END
) AS num
FROM
sms_msgid_log_01
GROUP BY
dest_teminal_id ,
msgid1
) t
先根据号码和流失号分组统计查询,然后再从子查询中查询,为5的就是都成功了,不为5的就是至少有一个失败了