项目的mysql查询统计

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