sql 求和、去重、合并、分组

一、求和

求和链接

	SELECT  ts_createtime ,c_biztype,s_orgcode FROM SPP_REQ_BASIC_INFO WHERE YEAR(ts_createtime)='2023'

执行结果

SELECT MONTH(ts_createtime) AS mth,s_orgcode,
	case c_biztype when '10' then 1 ELSE 0 END changgui, 
	case c_biztype when '20' then 1 ELSE 0 END dongtai, 
	case c_biztype when '30' then 1 ELSE 0 END pingzheng,
	case c_biztype when '40' then 1 ELSE 0 END dongjie,
	case c_biztype when '50' then 1 ELSE 0 END zhifu 
	FROM  (
		SELECT  ts_createtime ,c_biztype,s_orgcode FROM SPP_REQ_BASIC_INFO WHERE YEAR(ts_createtime)='2023'
	union
SELECT  ts_createtime ,'20' as c_biztype,s_orgcode FROM spp_req_dynamic_basic_info WHERE YEAR(ts_createtime)='2023'
	) u

执行结果

SELECT mth,
	SUM(changgui) changui,   
	SUM(dongtai) dongtai, 
	SUM(pingzheng) pingzheng, 
	SUM(dongjie) dongjie, 
	SUM(zhifu) zhifu 
FROM (          
	SELECT MONTH(ts_createtime) AS mth,s_orgcode,
	case c_biztype when '10' then 1 ELSE 0 END changgui, 
	case c_biztype when '20' then 1 ELSE 0 END dongtai, 
	case c_biztype when '30' then 1 ELSE 0 END pingzheng,
	case c_biztype when '40' then 1 ELSE 0 END dongjie,
	case c_biztype when '50' then 1 ELSE 0 END zhifu 
	FROM  (
		SELECT  ts_createtime ,c_biztype,s_orgcode FROM SPP_REQ_BASIC_INFO WHERE YEAR(ts_createtime)='2023'
	union
SELECT  ts_createtime ,'20' as c_biztype,s_orgcode FROM spp_req_dynamic_basic_info WHERE YEAR(ts_createtime)='2023'
	) u
) f
GROUP BY mth

按月份统计 数量 执行结果

二、合并去重统计

select * from spp_fb_freeze_info

表中数据

SELECT
	b.mth,
	sum( b.s_sdje ) AS je,
	count( DISTINCT s_kh ) sl 
FROM
	(
	SELECT
		fi.s_sdje,
		DATE_FORMAT( fi.ts_createtime, '%m' ) mth,
		fi.s_orgcode,
	CASE
			WHEN ( fi.s_kh IS NULL OR fi.s_kh = '' ) 
			AND ( fi.s_zh IS NOT NULL AND fi.s_zh != '' ) THEN
				fi.s_zh ELSE fi.s_kh 
			END s_kh 
FROM
	spp_fb_freeze_info fi
	LEFT JOIN spp_req_basic_info bi ON fi.s_bizid = bi.s_id 
WHERE
	fi.c_zxjg = '0' 
	AND bi.c_qqcslx IN ( '05', '06' ) 
	AND bi.c_biztype = '40' 
	AND YEAR ( bi.ts_createtime )= '2023' 
	) b 
GROUP BY
	mth	

按月份统计金额与数量 执行结果

三、补全、统计(分组)

SELECT a.c_datastatus AS cDatastatus,
	( CASE a.c_datastatus
	WHEN '0' THEN '待提交'
	WHEN '1' THEN '已提交'
	WHEN '2' THEN '复核通过'
	WHEN '3' THEN '复核不通过'
	WHEN '4' THEN '正在报送'
	WHEN '6' THEN '报送成功'
	WHEN '7' THEN '报送失败' END ) AS NAME,
	COUNT( a.c_datastatus ) AS num
	FROM eirs_tb_rating_info a
	
	where   a.c_datastatus != '5'
	 AND ((a.s_bustype = 'DELETE' AND a.c_datastatus != '6') or a.s_bustype != 'DELETE')
	 GROUP BY c_datastatus 

执行结果

	SELECT '0' AS cDatastatus,'待提交' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '1' AS cDatastatus,'已提交' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '2' AS cDatastatus,'复核通过' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '3' AS cDatastatus,'复核不通过' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '4' AS cDatastatus,'正在报送' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '6' AS cDatastatus,'报送成功' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '7' AS cDatastatus,'报送失败' AS NAME,0 AS num FROM dual UNION ALL
	SELECT a.c_datastatus AS cDatastatus,
	( CASE a.c_datastatus
	WHEN '0' THEN '待提交'
	WHEN '1' THEN '已提交'
	WHEN '2' THEN '复核通过'
	WHEN '3' THEN '复核不通过'
	WHEN '4' THEN '正在报送'
	WHEN '6' THEN '报送成功'
	WHEN '7' THEN '报送失败' END ) AS NAME,
	COUNT( a.c_datastatus ) AS num
	FROM eirs_tb_rating_info a
	
	where   a.c_datastatus != '5'
	 AND ((a.s_bustype = 'DELETE' AND a.c_datastatus != '6') or a.s_bustype != 'DELETE')
	 GROUP BY c_datastatus 

执行结果

 SELECT
	b.cDatastatus AS "cDatastatus",
	b.NAME AS "name",
	MAX( b.num ) AS "num"
	FROM
	(
	SELECT '0' AS cDatastatus,'待提交' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '1' AS cDatastatus,'已提交' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '2' AS cDatastatus,'复核通过' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '3' AS cDatastatus,'复核不通过' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '4' AS cDatastatus,'正在报送' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '6' AS cDatastatus,'报送成功' AS NAME,0 AS num FROM dual UNION ALL
	SELECT '7' AS cDatastatus,'报送失败' AS NAME,0 AS num FROM dual UNION ALL
	SELECT a.c_datastatus AS cDatastatus,
	( CASE a.c_datastatus
	WHEN '0' THEN '待提交'
	WHEN '1' THEN '已提交'
	WHEN '2' THEN '复核通过'
	WHEN '3' THEN '复核不通过'
	WHEN '4' THEN '正在报送'
	WHEN '6' THEN '报送成功'
	WHEN '7' THEN '报送失败' END ) AS NAME,
	COUNT( a.c_datastatus ) AS num
	FROM eirs_tb_rating_info a
	
	where   a.c_datastatus != '5'
	 AND ((a.s_bustype = 'DELETE' AND a.c_datastatus != '6') or a.s_bustype != 'DELETE')
	 GROUP BY c_datastatus ) b 
	 GROUP BY b.cDatastatus, b.NAME 
	 ORDER BY b.cDatastatus ASC

执行结果