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

表中数据

SELECTb.mth,sum( b.s_sdje ) AS je,count( DISTINCT s_kh ) sl 
FROM(SELECTfi.s_sdje,DATE_FORMAT( fi.ts_createtime, '%m' ) mth,fi.s_orgcode,CASEWHEN ( fi.s_kh IS NULL OR fi.s_kh = '' ) AND ( fi.s_zh IS NOT NULL AND fi.s_zh != '' ) THENfi.s_zh ELSE fi.s_kh END s_kh 
FROMspp_fb_freeze_info fiLEFT JOIN spp_req_basic_info bi ON fi.s_bizid = bi.s_id 
WHEREfi.c_zxjg = '0' AND bi.c_qqcslx IN ( '05', '06' ) AND bi.c_biztype = '40' AND YEAR ( bi.ts_createtime )= '2023' ) b 
GROUP BYmth	

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

三、补全、统计(分组)

SELECT a.c_datastatus AS cDatastatus,( CASE a.c_datastatusWHEN '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 numFROM eirs_tb_rating_info awhere   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 ALLSELECT '1' AS cDatastatus,'已提交' AS NAME,0 AS num FROM dual UNION ALLSELECT '2' AS cDatastatus,'复核通过' AS NAME,0 AS num FROM dual UNION ALLSELECT '3' AS cDatastatus,'复核不通过' AS NAME,0 AS num FROM dual UNION ALLSELECT '4' AS cDatastatus,'正在报送' AS NAME,0 AS num FROM dual UNION ALLSELECT '6' AS cDatastatus,'报送成功' AS NAME,0 AS num FROM dual UNION ALLSELECT '7' AS cDatastatus,'报送失败' AS NAME,0 AS num FROM dual UNION ALLSELECT a.c_datastatus AS cDatastatus,( CASE a.c_datastatusWHEN '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 numFROM eirs_tb_rating_info awhere   a.c_datastatus != '5'AND ((a.s_bustype = 'DELETE' AND a.c_datastatus != '6') or a.s_bustype != 'DELETE')GROUP BY c_datastatus 

执行结果

 SELECTb.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 ALLSELECT '1' AS cDatastatus,'已提交' AS NAME,0 AS num FROM dual UNION ALLSELECT '2' AS cDatastatus,'复核通过' AS NAME,0 AS num FROM dual UNION ALLSELECT '3' AS cDatastatus,'复核不通过' AS NAME,0 AS num FROM dual UNION ALLSELECT '4' AS cDatastatus,'正在报送' AS NAME,0 AS num FROM dual UNION ALLSELECT '6' AS cDatastatus,'报送成功' AS NAME,0 AS num FROM dual UNION ALLSELECT '7' AS cDatastatus,'报送失败' AS NAME,0 AS num FROM dual UNION ALLSELECT a.c_datastatus AS cDatastatus,( CASE a.c_datastatusWHEN '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 numFROM eirs_tb_rating_info awhere   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

执行结果

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！