转载

查询同一组的最大最小值及明细

数据库环境：Oracle11g

现有某监测站的监测数据如下表。要求：查询出每个测站的水位最大最小值及最大最小值对应的时间，

如果最大、最小值存在重复，则只显示重复数据中最新时间的那条数据。

实现思路：

根据测站和水位分组，按最新时间排序，有重复的只取最新时间的数据
分别求得最大、最小值的所在数据行，然后union

准备建表，导入数据的脚本

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);

View Code

数据有了，现在我们来实现这个查询

1.过滤重复数据

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)

View Code

2.union最大、最小值所在行的数据集

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

View Code

当然，我们也可以对union操作近一步简化，直接去掉union操作

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)