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结束-------------------------------
  1. 结果展示
    在这里插入图片描述
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL中的开窗函数是一种强大的功能,可以根据指定的条件对查询结果进行分组、排序和聚合操作。开窗函数可以用于计算每个分组内的聚合值,并且可以在查询结果中返回每个行的详细信息。这使得开窗函数在处理复杂的分析和报表查询时非常有用。 在MySQL中,开窗函数的语法遵循标准的SQL语法。您可以在MySQL 8.0的官方文档中找到有关开窗函数的详细信息和示例用法。 开窗函数可以根据其功能进行分类。常见的开窗函数包括聚合开窗函数、排序开窗函数和其他类型的开窗函数。聚合开窗函数用于计算聚合值,比如求和、平均值等。排序开窗函数用于根据指定的条件对结果集进行排序。其他类型的开窗函数可以根据具体需求进行自定义的操作。 一个常见的示例是使用SUM函数作为聚合开窗函数,对每个分组内的特定列进行求和。例如,在一个名为"linux"的表中,我们可以使用SUM函数计算每个name分组内的cnt列的总和,并使用开窗函数在查询结果中返回每一行的详细信息。 我希望这个回答能帮助到您理解MySQL中的开窗函数。如果您需要更多信息,请参考MySQL 8.0的官方文档。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [MySQL开窗函数](https://blog.csdn.net/mr__sun__/article/details/124257213)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* [mysql开窗函数](https://blog.csdn.net/m0_46926492/article/details/124236167)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值