一、求和
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
执行结果