Mysql强大的开窗函数Demo1

开窗函数Demo1

1. 业务场景

   每天产生四种血型的最新库存数量，需要根据年月排序拿到最新一天的库存量

2. 数据库准备

   CREATE TABLE `yl461_blood_station_storagedetail` (
     `ID` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '平台血站血液库存编号',
     `BLOODVARIETIESCODE` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '血液品种代码',
     `BLOODVARIETIES` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '血液品种',
     `BLOODTYPECODE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '血型代码',
     `RHTYPE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'RH(D)血型代码',
     `BLOODTYPE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '血型',
     `COUNT` int DEFAULT NULL COMMENT '数量',
     `UNIT` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '计量单位',
     `IDENT` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '有效标识 1-否 2-是',
     `DJSJ` datetime DEFAULT NULL COMMENT '登记时间(系统)',
     `DJJG_DM` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '登记机构代码',
     PRIMARY KEY (`DJJG_DM`,`ID`) USING BTREE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='血液库存信息';
   
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('1', '1', '1', '1', '1', '8.3日A型', 3, 'ml', '2', '2021-08-03 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('11', '1', '1', '2', '1', '8.3日B型', 3, 'ml', '2', '2021-08-03 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('12', '1', '1', '2', NULL, '8.23日B型', 4, 'ml', '2', '2021-08-23 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('13', '1', '1', '2', NULL, '8.26日B型', 5, 'ml', '2', '2021-08-26 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('14', '1', '1', '3', NULL, '8.3日O型', 6, 'ml', '2', '2021-08-03 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('15', '1', '1', '3', NULL, '8.23日O型', 7, 'ml', '2', '2021-08-23 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('16', '1', '1', '3', NULL, '8.26日O型', 8, 'ml', '2', '2021-08-26 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('2', '1', '1', '1', NULL, '8.23日A型', 23, 'ml', '2', '2021-08-23 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('3', '1', '1', '1', NULL, '8.26日A型', 20, 'ml', '2', '2021-08-26 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('4', '1', '1', '1', NULL, '11', 36, 'ml', '2', '2021-07-26 15:42:54', 'csxz');
   INSERT INTO bdop.yl461_blood_station_storagedetail
   (ID, BLOODVARIETIESCODE, BLOODVARIETIES, BLOODTYPECODE, RHTYPE, BLOODTYPE, COUNT, UNIT, IDENT, DJSJ, DJJG_DM)
   VALUES('5', '1', '1', '1', NULL, '11', 33, 'ml', '2', '2021-07-06 15:42:54', 'csxz');
   

3. Sql实现

-- 1、sql开始-----------------------------
with t as(
			-- 这里拿到根据djsj倒序之后的第1条数据
			select date_format(a.djsj,'%Y-%m') yearMonth,
					substring_index(group_concat(case when a.bloodtypecode='1' then a.count else 0 end order by a.djsj desc ),',',1) bloodA,
					substring_index(group_concat(case when a.bloodtypecode='2' then a.count else 0 end order by a.djsj desc ),',',1) bloodB,
					substring_index(group_concat(case when a.bloodtypecode='3' then a.count else 0 end order by a.djsj desc ),',',1) bloodO,
					substring_index(group_concat(case when a.bloodtypecode='4' then a.count else 0 end order by a.djsj desc ),',',1) bloodAB
			from yl461_blood_station_storagedetail a
			group by a.bloodtypecode,date_format(a.djsj,'%Y-%m') )
select t.yearMonth,ifnull(sum(bloodA+bloodB+bloodO+bloodAB),0) capacity,
			max(t.bloodA) bloodA,max(t.bloodB) bloodB,
			max(t.bloodO) bloodO,max(t.bloodAB) bloodAB
from t
group by t.yearMonth
order by t.yearMonth desc
-- 1、sql结束-------------------------------

-- 2、sql开始，强大的开窗实现方式------------------
with t as(
			SELECT date_format(djsj,'%Y-%m') yearMonth, bloodtypecode, count,
					case when bloodtypecode='1' then count end bloodA,  
					case when bloodtypecode='2' then count end bloodB,  
					case when bloodtypecode='3' then count end bloodO,  
					case when bloodtypecode='4' then count end bloodAB,		
					ROW_NUMBER() over ( PARTITION BY bloodtypecode,date_format(djsj,'%Y-%m') ORDER BY djsj DESC ) AS rn 
			FROM yl461_blood_station_storagedetail )
select  t.yearMonth,
		ifnull(max(t.bloodA),0) as bloodA,ifnull(max(t.bloodB),0) as bloodB,
		ifnull(max(t.bloodO),0) as bloodO,ifnull(max(t.bloodAB),0) as bloodAB
from t
WHERE rn = 1
group by t.yearMonth
order by t.yearMonth desc
-- 2、sql结束-------------------------------

4. 结果展示