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

数据库环境：Oracle11g

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

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

实现思路：

1. 根据测站和水位分组，按最新时间排序，有重复的只取最新时间的数据
2. 分别求得最大、最小值的所在数据行，然后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);

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

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)

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

当然，我们也可以对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)

好了，现在展示一下查询结果

（本文完）