Oracle/MySQL之分组后按条件汇总SQL

最新推荐文章于 2023-08-29 08:02:34 发布

你看起来很好笑.

最新推荐文章于 2023-08-29 08:02:34 发布

阅读量1k

点赞数 1

分类专栏： SQL 文章标签：数据库 oracle mysql sql

本文链接：https://blog.csdn.net/ITliuht/article/details/126519571

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Oracle/MySQL之分组后按条件汇总:
场景：
首先，我有一张名为要货通知的表（exe_shipment_notice_b），他的字段信息如下
在这里插入图片描述
表中数据如下：

需求：制作一张报表，将8月份前14天的每个物料的要货数量按天汇总，再将[08-15，08-21]、[08-22，08-31]两个日期区间内的要货数量汇总，最后将9月，10月的要货数量汇总，嗯，需求听起来很简单。下面开始写SQL。

解决方案：
不废话了，直接上SQL:
MySQL版：

SELECT
	a.MATERIAL_CODE AS MATERIAL_CODE,
	a.MATERIAL_CODE_SHOW AS MATERIAL_CODE_SHOW,
	sum( a.days1 ) AS days1,
	sum( a.days2 ) AS days2,
	sum( a.days3 ) AS days3,
	sum( a.days4 ) AS days4,
	sum( a.days5 ) AS days5,
	sum( a.days6 ) AS days6,
	sum( a.days7 ) AS days7,
	sum( a.days8 ) AS days8,
	sum( a.days9 ) AS days9,
	sum( a.days10 ) AS days10,
	sum( a.days11 ) AS days11,
	sum( a.days12 ) AS days12,
	sum( a.days13 ) AS days13,
	sum( a.days14 ) AS days14,
	sum( a.第三周 ) AS 第三周,
	sum( a.第四周 ) AS 第四周,
	sum( a.2022年9月 ) AS 2022年9月,
	sum( a.2022年9月 ) AS 2022年9月 
FROM
	(
	SELECT
		exe_shipment_notice_b.MATERIAL_CODE AS MATERIAL_CODE,
		exe_shipment_notice_b.MATERIAL_CODE_SHOW AS MATERIAL_CODE_SHOW,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-01',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days1,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-02',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days2,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-03',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days3,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-04',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days4,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-05',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days5,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-06',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days6,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-07',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days7,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-08',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days8,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-09',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days9,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-10',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days10,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-11',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days11,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-12',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days12,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-13',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days13,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))= '2022-08-14',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS days14,
	IF
		(((
					date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))>= '2022-08-15' 
				)&&((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))<= '2022-08-21' 
				),
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS 第三周,
	IF
		(((
					date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))>= '2022-08-22' 
				)&&((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))<= '2022-08-31' 
				),
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS 第四周,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m' ))= '2022-09',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS 2022年9月,
	IF
		((
				date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m' ))= '2022-10',
			sum( exe_shipment_notice_b.required_qty ),
			'-' 
		) AS 2022年10月 
	FROM
		exe_shipment_notice_b exe_shipment_notice_b 
	GROUP BY
		exe_shipment_notice_b.MATERIAL_CODE,
		(date_format( date_add( str_to_date( exe_shipment_notice_b.required_date, '%Y-%m-%dT%H:%i:%s' ), INTERVAL 8 HOUR ), '%Y-%m-%d' ))) AS a 
GROUP BY
	a.MATERIAL_CODE

MySQL实现起来挺简单的，主要是用的比较多哈，IF()函数用起来很方便。由于本人学艺不精，SQL看起来就很低级，但是满足需求也勉强能接受。

Oracle版：

SELECT b.* FROM (SELECT 
a.MATERIAL_CODE AS MATERIAL_CODE,
a.MATERIAL_CODE_SHOW AS MATERIAL_CODE_SHOW,
SUM(a.required_qty1) AS required_qty1,
SUM(a.required_qty2) AS required_qty2,
SUM(a.required_qty3) AS required_qty3,
SUM(a.required_qty4) AS required_qty4,
SUM(a.required_qty5) AS required_qty5,
SUM(a.required_qty6) AS required_qty6,
SUM(a.required_qty7) AS required_qty7,
SUM(a.required_qty8) AS required_qty8,
SUM(a.required_qty9) AS required_qty9,
SUM(a.required_qty10) AS required_qty10,
SUM(a.required_qty11) AS required_qty11,
SUM(a.required_qty12) AS required_qty12,
SUM(a.required_qty13) AS required_qty13,
SUM(a.required_qty14) AS required_qty14,
SUM(a.required_qty15) AS required_qty15,
SUM(a.required_qty16) AS required_qty16,
SUM(a.required_qty17) AS required_qty17,
SUM(a.required_qty18) AS required_qty18 
 FROM (SELECT 
 exe_shipment_notice_b.MATERIAL_CODE AS MATERIAL_CODE,
 exe_shipment_notice_b.MATERIAL_CODE_SHOW AS MATERIAL_CODE_SHOW,
 TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd') AS required_date,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-01',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty1,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-02',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty2,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-03',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty3,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-04',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty4,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-05',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty5,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-06',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty6,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-07',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty7,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-08',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty8,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-09',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty9,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-10',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty10,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-11',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty11,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-12',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty12,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-13',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty13,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd'), '2022-08-14',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty14,
	 sum(
	case 
	when (TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd')>='2022-08-15') and (TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd')<='2022-08-21') then 
	exe_shipment_notice_b.required_qty 
	else
	0
	end
	) required_qty15,
sum(
	case 
	when (TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd')>='2022-08-22') and (TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM-dd')<='2022-08-31') then 
	exe_shipment_notice_b.required_qty 
	else
	0
	end
	) required_qty16,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM'), '2022-09',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty17,
 decode(TO_CHAR(TO_DATE(REPLACE(SUBSTR(exe_shipment_notice_b.required_date, 1, 19), 'T', ' '),'yyyy-MM-dd HH24:mi:ss')+8/24 , 'yyyy-MM'), '2022-10',sum(exe_shipment_notice_b.required_qty ),0) AS required_qty18
 
FROM
	exe_shipment_notice_b exe_shipment_notice_b 
GROUP BY exe_shipment_notice_b.MATERIAL_CODE,exe_shipment_notice_b.MATERIAL_CODE_SHOW,required_date) a 
GROUP BY a.MATERIAL_CODE,a.MATERIAL_CODE_SHOW) b

Oracle版我写起来还是费了老大的劲的，原因是之前没怎么了解过，只好临阵磨枪了。其实这和MySQL原理差不多，问题是Oracle和MySQL有些函数不一样，比如MySQL中的IF()函数，在Oracle里只能用类似的decode()函数来代替。其次是Oracle的分组（GROUP BY）语句，这个分组语句有一个要求，就是你要查询出来的字段必须要在GROUP BY中出现，否则就会报错。

最后看看报表的效果：