查询一年每个月的数据和当月的数据

最新推荐文章于 2023-07-31 16:21:46 发布

RyFit

最新推荐文章于 2023-07-31 16:21:46 发布

阅读量321

点赞数 1

分类专栏： SQL记录

本文链接：https://blog.csdn.net/qq_45619439/article/details/119750186

版权

SQL记录专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一年每个月的SQL:

SELECT
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '01', 1, 0 ) ) "01",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '02', 1, 0 ) ) "02",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '03', 1, 0 ) ) "03",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '04', 1, 0 ) ) "04",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '05', 1, 0 ) ) "05",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '06', 1, 0 ) ) "06",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '07', 1, 0 ) ) "07",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '08', 1, 0 ) ) "08",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '09', 1, 0 ) ) "09",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '10', 1, 0 ) ) "10",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '11', 1, 0 ) ) "11",
		SUM( DECODE( TO_CHAR( B.CJRQ, 'MM' ), '12', 1, 0 ) ) "12"
	FROM
		ZHBG_KYJFKZ_LXCGSQ B
	WHERE
		TO_CHAR( B.CJRQ, 'yyyy' ) = #{date}

当月的数的SQL:

SELECT
		TO_CHAR(A.FDATE, 'dd') "fdate", NVL(B.FNUM, 0) "fnum"
		FROM
		(
		SELECT
		FDATE
		FROM
		(SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 AS FDATE FROM DUAL CONNECT BY LEVEL <![CDATA[<=]]> 31) T
		WHERE
		TO_CHAR(FDATE, 'MM') = TO_CHAR(SYSDATE, 'MM')
		) A
		LEFT JOIN(
		SELECT
		TO_CHAR(B.CJRQ, 'dd') AS FDATE,
		COUNT(1) AS FNUM
		FROM
		ZHBG_KYJFKZ_LXCGSQ B
		WHERE
		TO_CHAR(B.CJRQ, 'yyyy-MM') = TO_CHAR(SYSDATE, 'yyyy-MM')
		GROUP BY
		TO_CHAR(B.CJRQ, 'dd')
		) B ON TO_CHAR(A.FDATE, 'dd') = B.FDATE
		ORDER BY
		A.FDATE