查询一年1、1-2月、1-3~一直到1-12月
运用场景:查询参数年份(year)的累计同比
SQL:
// MONTHLY是伪表,存年月(有图)
WITH MONTHLY AS (
SELECT
'2022-01' AS STARTTIME, '2022-02' AS ENDTIME
FROM DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-03' AS ENDTIME
FROM DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-04' AS ENDTIME
FROM DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-05' AS ENDTIME
FROM DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-06' AS ENDTIME
FROM
DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-07' AS ENDTIME
FROM
DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-08' AS ENDTIME
FROM
DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-09' AS ENDTIME
FROM
DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-10' AS ENDTIME
FROM
DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-11' AS ENDTIME
FROM
DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2022-12' AS ENDTIME
FROM
DUAL UNION ALL
SELECT
'2022-01' AS STARTTIME, '2023-01' AS ENDTIME
FROM
DUAL
),
//此处是业务表,根据自己需要来查询
BASE AS (
SELECT B.PROJECT_UUID, B.TOTAL_MONEY, A.APPLY_TIME
FROM
//此处是项目基本信息表
APPRT_PROJECT_INFO A
//此处是项目基本信息业务表
LEFT JOIN APPRT_PROJECT_INFO_BUSINESS B
//两张表的关联字段
ON B.PROJECT_UUID = A.PROJECT_UUID
//需要增加的查询条件
WHERE 1 = 1
AND A.IS_DEL = '0'
//此处是需要查询的年份
AND TO_CHAR( A.APPLY_TIME, 'yyyy' ) = '2022'
AND a.DEAL_CODE IS NOT NULL
//条件(行业对应的值 例如01对应101、102、103所以用in)
AND b.INDUSTRY IN (
SELECT DISTINCT
b.INDUSTRY_CODE
FROM
TZ_SPECIAL_CATALOG a
LEFT JOIN TZ_SPECIAL_INDUSTRY_MAPPING b
ON a.CATALOG_UUID = b.CATALOG_UUID
WHERE
a.IS_DEL = '0'
AND a.is_valid = 'T'
AND a.catalog_level = '2'
AND a.CATALOG_CODE = '01'
)
AND a.IS_DEL = '0'
AND B.TOTAL_MONEY < '50000.0'
)
//查询分组后的所有数据、所有金额相加后的值
SELECT M.ENDTIME, COUNT( M.ENDTIME ) num,
//DECODE相当于 case when,此处的意思是相加,如果是null转换成0
DECODE( COUNT( B.TOTAL_MONEY ), 0, 0, SUM( NVL( B.TOTAL_MONEY, 0 ) ) ) money
FROM
MONTHLY M LEFT JOIN BASE B ON
//查询时间区间内的
B.APPLY_TIME >= TO_DATE( M.STARTTIME, 'yyyy-mm' )
AND B.APPLY_TIME < TO_DATE( M.ENDTIME, 'yyyy-mm' )
GROUP BY
M.ENDTIME
ORDER BY
M.ENDTIME ASC
![MONTHLY伪表](https://img-blog.csdnimg.cn/4c2c6fa4f5034897b8de5492e43c9735.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDIxOTY2NDM=,size_12,color_FFFFFF,t_70,g_se,x_16#pic_center)
![查询到的结果图](https://img-blog.csdnimg.cn/974f133f0bb44041971d4e841ec9e129.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDIxOTY2NDM=,size_15,color_FFFFFF,t_70,g_se,x_16#pic_center)
安利小知识:Oracle
where是从后往前执行的,可以将过滤数据量大的条件放在后面(过滤量:比方说有两个条件【一个可以过滤掉10000条数据、另一个可以过滤500条,过滤10000条的大就放在后面,可以加快sql执行效率,优化sql的优先选择】)