主页 / dba / 问题 / 46870
Accepted
riz
Asked: 2013-07-24 07:26:46 +0800 CST

选择具有最大日期或最晚日期的日期

  • 772

这里有两张表。

学校员工

SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID
=================================================================
ABE           Principal         24-JAN-13               111222
ABE           Principal         09-FEB-12               222111

人员

PERSON_ID + NAME
=================
111222      ABC
222111      XYZ

这是我的 Oracle 查询。

SELECT MAX(LAST_UPDATE_DATE_TIME) AS LAST_UPDATE, SCHOOL_CODE, PERSON_ID
FROM SCHOOL_STAFF
WHERE STAFF_TYPE_NAME='Principal'
GROUP BY SCHOOL_CODE, PERSON_ID
ORDER BY SCHOOL_CODE;

这给出了这个结果

LAST_UPDATE SCHOOL_CODE PERSON_ID
===========+===========+=========
24-JAN-13   ABE         111222
09-FEB-12   ABE         222111

我想为日期最晚的学校选择第一个。

谢谢。

oracle greatest-n-per-group

3 个回答

  1. Best Answer

    您当前的查询没有给出期望的结果，因为您GROUP BY在列上使用了一个子句，该子句对PERSON_ID两个条目都具有唯一值。结果，您将返回两行。

    有几种方法可以解决这个问题。您可以使用子查询来应用聚合函数以返回max(LAST_UPDATE_DATE_TIME)for each SCHOOL_CODE：

    select s1.LAST_UPDATE_DATE_TIME,
  s1.SCHOOL_CODE,
  s1.PERSON_ID
from SCHOOL_STAFF s1
inner join
(
  select max(LAST_UPDATE_DATE_TIME) LAST_UPDATE_DATE_TIME,
    SCHOOL_CODE
  from SCHOOL_STAFF
  group by SCHOOL_CODE
) s2
  on s1.SCHOOL_CODE = s2.SCHOOL_CODE
  and s1.LAST_UPDATE_DATE_TIME = s2.LAST_UPDATE_DATE_TIME;

    请参阅带有演示的 SQL Fiddle

    或者，您可以使用窗口函数返回每所学校最新的数据行LAST_UPDATE_DATE_TIME：

    select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
  select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
    row_number() over(partition by SCHOOL_CODE 
                        order by LAST_UPDATE_DATE_TIME desc) seq
  from SCHOOL_STAFF
  where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;

    请参阅带有演示的 SQL Fiddle

    此查询实现row_number()为分区中的每一行分配一个唯一编号，SCHOOL_CODE并根据LAST_UPDATE_DATE_TIME.

    附带说明一下，带有聚合函数的 JOIN 与版本不完全相同row_number()。如果您有两行具有相同的事件时间，则 JOIN 将返回两行，而row_number()将只返回一个。如果您想使用窗口函数返回两者，请考虑使用rank()窗口函数，因为它将返回关系：

    select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
  select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
    rank() over(partition by SCHOOL_CODE 
                        order by LAST_UPDATE_DATE_TIME desc) seq
  from SCHOOL_STAFF
  where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;

    看演示

    • 30

  2. 我很惊讶没有人利用 row_number() 之外的窗口函数

    这里有一些数据可以玩：

    CREATE TABLE SCHOOL_STAFF
(
LAST_UPDATE_DATE_TIME VARCHAR(20),
SCHOOL_CODE VARCHAR(20),
PERSON_ID VARCHAR(20),
STAFF_TYPE_NAME VARCHAR(20)
);
INSERT INTO SCHOOL_STAFF VALUES ('24-JAN-13', 'ABE', '111222', 'Principal');
INSERT INTO SCHOOL_STAFF VALUES ('09-FEB-12', 'ABE', '222111', 'Principal');

    OVER() 子句创建一个窗口，您将为其定义聚合组。在这种情况下，我只对 SHOOL_CODE 进行分区，因此我们将看到 FIRST_VALUE，它来自 LAST_UPDATE_DATE_TIME，按 SCHOOL_CODE 分组，并按 LAST_UPDATE_DATE_TIME 降序排列。该值将应用于每个 SCHOOL_CODE 的整个列。

    密切注意 over() 子句中的分区和排序非常重要。 

    SELECT DISTINCT
 FIRST_VALUE(LAST_UPDATE_DATE_TIME) OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS LAST_UPDATE
,FIRST_VALUE(SCHOOL_CODE)           OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS SCHOOL_CODE
,FIRST_VALUE(PERSON_ID)             OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS PERSON_ID
FROM SCHOOL_STAFF
WHERE STAFF_TYPE_NAME = 'Principal'
ORDER BY SCHOOL_CODE

    回报：

    24-JAN-13   ABE 111222

    这应该在很大程度上消除了您对 GROUP BY 和子查询的需求。不过，您需要确保包含 DISTINCT。

    • 5 
  3. select LAST_UPDATE_DATE_TIME as LAST_UPDATE,
  SCHOOL_CODE,
  PERSON_ID
from SCHOOL_STAFF
WHERE STAFF_TYPE_NAME='Principal'
AND LAST_UPDATE_DATE_TIME = (SELECT MAX(LAST_UPDATE_DATE_TIME)
                            FROM SCHOOL_STAFF s2
                            WHERE PERSON_ID = s2.PERSON_ID)
    • 2

相关问题