select t.org_id,o.org_name, t.qty_total,
t.qty_free,
t.qty_free / t.qty_total as ratio_free,
t.qty_unknow,
t.qty_unknow / t.qty_total as ratio_unknow,
t.qty_total - t.qty_free - qty_unknow as qty_fail,
(t.qty_total - t.qty_free - qty_unknow) / t.qty_total as ratio_fail
from (
select sr.org_id, count(1) as qty_total,
count( case when sr.free = 1 then 1 end) as qty_free,
count( case when sr.status_code = 'CX:0000' then 1 end) as qty_unknow
from sms_send_result_202109 sr
where sr.submit_time between '2021-09-20 11:28:00' and '2021-09-21 00:00:00'
group by sr.org_id
) t
left join sys_org o on o.id = t.org_id
先查临时表,得到临时表后再统计
select t.org_id,o.org_name,t.channel_id,c.channel_name,t.qty,t.qty_free,t.qty_unknow from (
select sr.org_id,sr.channel_id,count(1) as qty,
count( CASE WHEN sr.free = 1 THEN 1 END ) AS qty_free,
count( CASE WHEN sr.status_code = 'CX:0000' THEN 1 END ) AS qty_unknow
from sms_send_result sr
where sr.submit_time between "2020-11-12 00:00:00" and "2021-11-12 00:00:00" and sr.org_id = 2
group by sr.org_id,sr.channel_id
) t
left join sys_org o ON o.id = t.org_id
LEFT JOIN sms_channel c ON c.id = t.channel_id
SQL的联合查询
SELECT status_code,status_desc,COUNT(1) qty FROM sms_send_result_202109 sr WHERE sr.submit_time BETWEEN '2021-09-19 09:28:00' AND '2021-09-19 10:28:00'
GROUP BY status_code,status_desc
UNION
SELECT 'xxx' AS status_code,'失败总数' AS status_desc,COUNT(1) qty FROM sms_send_result_202109 sr WHERE sr.submit_time BETWEEN '2021-09-19 09:28:00' AND '2021-09-19 10:28:00'
AND status_code NOT IN ('0' ,'CX:0000')
UNION
SELECT 'total' AS status_code,'总数' AS status_desc,COUNT(1) qty FROM sms_send_result_202109 sr WHERE sr.submit_time BETWEEN '2021-09-19 09:28:00' AND '2021-09-19 10:28:00'
SQL的状态查询
SET @timeB = '2021-09-18 21:28:00';
SET @timeE = '2021-09-19 00:00:00';
SELECT
@total := COUNT( 1 )
FROM
sms_send_result_202109 sr
WHERE
sr.submit_time BETWEEN @timeB
AND @timeE;
SELECT
status_code,
status_desc,
COUNT( 1 ) qty,
COUNT( 1 )/ @total AS ratio
FROM
sms_send_result_202109 sr
WHERE
sr.submit_time BETWEEN @timeB
AND @timeE
AND sr.org_id = 7
GROUP BY
status_code,
status_desc UNION
SELECT
'xxx' AS status_code,
'失败总数' AS status_desc,
COUNT( 1 ) qty,
COUNT( 1 ) / @total AS ratio
FROM
sms_send_result_202109 sr
WHERE
sr.submit_time BETWEEN @timeB
AND @timeE
AND sr.org_id = 7
AND status_code NOT IN ( '0', 'CX:0000', '1000' ) UNION
SELECT
'total' AS status_code,
'总数' AS status_desc,
@total AS qty,
COUNT( 1 ) / @total AS ratio
FROM
sms_send_result_202109 sr
WHERE
sr.submit_time BETWEEN @timeB
AND @timeE
AND sr.org_id =7
SELECT
temp.org_id,
so.org_name AS orgName,
temp.countNum AS totalNum,
temp.free,
temp.unknow_num,
sms_channel.channel_name
FROM
(
SELECT
sr.org_id AS org_id,
sr.channel_id AS channel_id,
count( 1 ) AS countNum,
count( CASE WHEN sr.free = 1 THEN 1 END ) AS free,
count( CASE WHEN sr.status_code = 'CX:0000' THEN 1 END ) AS unknow_num
FROM
sms_send_result sr
WHERE
sr.submit_time BETWEEN '2021-10-01 00:00:00'
AND '2021-10-28 00:00:00'
GROUP BY
sr.org_id
) temp
LEFT JOIN sys_org so ON so.id = temp.org_id
LEFT JOIN sms_channel ON sms_channel.id = temp.channel_id
3、先按机构分组,再按通道分组,查到结果作为临时表
临时表再联合查询其他字段
SELECT
temp.org_id,
so.org_name AS orgName,
temp.countNum AS totalNum,
temp.free,
temp.unknow_num,
sms_channel.channel_name
FROM
(
SELECT
sr.org_id AS org_id,
sr.channel_id AS channel_id,
count( 1 ) AS countNum,
count( CASE WHEN sr.free = 1 THEN 1 END ) AS free,
count( CASE WHEN sr.status_code = 'CX:0000' THEN 1 END ) AS unknow_num
FROM
sms_send_result_202110 sr
WHERE
sr.submit_time BETWEEN '2020-10-25 00:00:00'
AND '2021-10-28 00:00:00'
GROUP BY
sr.org_id,
sr.channel_id
) temp
LEFT JOIN sys_org so ON so.id = temp.org_id
LEFT JOIN sms_channel ON sms_channel.id = temp.channel_id