查询同一组的最大最小值及明细
数据库环境:Oracle11g
现有某监测站的监测数据如下表。要求:查询出每个测站的水位最大最小值及最大最小值对应的时间,
如果最大、最小值存在重复,则只显示重复数据中最新时间的那条数据。
实现思路:
- 根据测站和水位分组,按最新时间排序,有重复的只取最新时间的数据
- 分别求得最大、最小值的所在数据行,然后union
准备建表,导入数据的脚本
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table dse_river_r(stcd varchar2(8),tm date,z number(6,2)) insert into dse_river_r values('80520508',to_date('2013-07-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),10.02); insert into dse_river_r values('80520508',to_date('2013-07-01 08:00:00','yyyy-mm-dd hh24:mi:ss'),9.98); insert into dse_river_r values('80520508',to_date('2013-07-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),10.02); insert into dse_river_r values('60407758',to_date('2013-07-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),9.00); insert into dse_river_r values('60407758',to_date('2013-07-01 08:00:00','yyyy-mm-dd hh24:mi:ss'),10.00); insert into dse_river_r values('60407758',to_date('2013-07-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),12.00); insert into dse_river_r values('60407725',to_date('2013-07-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),9.42); insert into dse_river_r values('60407725',to_date('2013-07-01 08:00:00','yyyy-mm-dd hh24:mi:ss'),9.42); insert into dse_river_r values('60407725',to_date('2013-07-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),9.42); insert into dse_river_r values('60507426',to_date('2013-07-01 10:00:00','yyyy-mm-dd hh24:mi:ss'),12.00); insert into dse_river_r values('60507426',to_date('2013-07-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),8.32); insert into dse_river_r values('60507426',to_date('2013-07-01 08:00:00','yyyy-mm-dd hh24:mi:ss'),12.00); insert into dse_river_r values('60507426',to_date('2013-07-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),9.10);
数据有了,现在我们来实现这个查询
1.过滤重复数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
WITH x0 AS (SELECT STCD, TM, Z, ROW_NUMBER () OVER (PARTITION BY stcd, z ORDER BY tm DESC) AS rn FROM dse_river_r), x1 AS (SELECT STCD, TM, Z FROM x0 WHERE rn = 1)
2.union最大、最小值所在行的数据集
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT STCD, tm, z FROM (SELECT STCD, tm, z, MAX (z) OVER (PARTITION BY stcd) AS z_max FROM x1) WHERE z = z_max UNION SELECT STCD, tm, z FROM (SELECT STCD, tm, z, MIN (z) OVER (PARTITION BY stcd) AS z_min FROM x1) WHERE z = z_min
当然,我们也可以对union操作近一步简化,直接去掉union操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
WITH x0 AS (SELECT STCD, TM, Z, ROW_NUMBER () OVER (PARTITION BY stcd, z ORDER BY tm DESC) AS rn FROM dse_river_r), x1 AS (SELECT STCD, TM, Z, MAX (z) OVER (PARTITION BY stcd) AS z_max, MIN (z) OVER (PARTITION BY stcd) AS z_min FROM x0 WHERE rn = 1) SELECT STCD, TM, Z FROM x1 WHERE z IN (z_max, z_min)
好了,现在展示一下查询结果
(本文完)