HAVING - GROUP BY获得最新的纪录 [英] HAVING - GROUP BY to get the latest record

问题描述

我有一张名为像这样的表：

  RESULTS_IDN名称主题年份合格
 1 MARK ENGLISH 1989 N 
 3 MARK英语1991 N 
 5 MARK英语1993 Y 
 7 MARK英语1995 N 
 2 MARK数学1990 N 
 5 MARK数学1993 N 
 6 MARK MATH 1995 Y 
 4 MARK SCIENCE 1991 N 
 9 MARK SCIENCE 1997 Y 
  

我需要知道他写的最新考试的主题候选人的资格状态，我该如何为这个（ORACLE / MSSQL）编写一个查询？

例如输入

 名称，主题输出名称IDN主题年份Q 
标记，英语输出标记7英语1995 N 
马克科学输出标记9科学1997 Y 
马克数学输出标记6数学1995 Y 
  

我知道解决此问题的一种方法。

$ b $

（选择名称主题年份最大（年）年份
来自结果其中名称='MARK'和SUBJECT =' MATH'
GROUP BY NAME SUBJECT YEAR）最新

将上面的表格加入IDN同一张桌子和我可以得到结果。但这是双重工作。无论如何，我可以使用HAVING CLAUSE或其他东西来使用MAX（YEAR）并获得相应的年份？我需要对GROUP BY数据进行2次操作，一次最新操作以及符合条件的合格状态。

当然，在DB中有100个这样的候选人的记录。

更新：这个问题是也被归类为每个群体中最大的问题。2.有趣的是知道它是DB中的一个分类问题。

解决方案在Oracle和SQL Server中，您可以使用分析/窗口函数RANK（）或ROW_NUMBER（）来实现此目的：

  select * 
 from（选择a。* 
，rank（）over（按名称分区，按年降序排列主题）rnk 
 from ... a 
）
其中rnk = 1 
  

RANK（）将返回1这是最新的每行名称和主题，ROW_NUMBER（）将返回一个随机行。

仅在Oracle中，您可以使用 KEEP to gi给你同样的结果：

  select name，subject，max（year）as year 
，max（qualified）保存（dense_rank first order order by year desc）作为符合条件的
从... 
按名称，主题
  

I have a table called RESULTS like this :

RESULTS_IDN    NAME    SUBJECT    YEAR    QUALIFIED
1              MARK    ENGLISH    1989    N
3              MARK    ENGLISH    1991    N
5              MARK    ENGLISH    1993    Y
7              MARK    ENGLISH    1995    N
2              MARK    MATH       1990    N
5              MARK    MATH       1993    N
6              MARK    MATH       1995    Y
4              MARK    SCIENCE    1991    N
9              MARK    SCIENCE    1997    Y

I need to know the Qualification Status of the CANDIDATE for a SUBJECT for the LATEST exam he has written , how do I write a query for this (ORACLE/MSSQL) ?

For example Input

NAME,SUBJECT  OUTPUT NAME IDN SUBJECT YEAR Q
MARK,ENGLISH  OUTPUT MARK 7   ENGLISH 1995 N 
MARK SCIENCE  OUTPUT MARK 9   SCIENCE 1997 Y
MARK MATH     OUTPUT MARK 6   MATH    1995 Y

I know of one way to solve this .

(SELECT NAME SUBJECT YEAR MAX(YEAR) YEAR
FROM RESULTS WHERE NAME = 'MARK' AND SUBJECT ='MATH'
GROUP BY NAME SUBJECT YEAR) LATEST 

Join the above table back on IDN to the same table and I can get the results . But this is double work . Is there anyway I can club the MAX(YEAR) and get the CORRESPONDING YEAR using HAVING CLAUSE or something ? I need 2 operations on the GROUP BY data , one Latest , and the corresponidng Qualified status .

PS : Ofcourse there are records for 100 candidates like this in the DB .

Update : This question is also categorized as greatest-n-per-group problem as per answer 2. Interesting to know it is a classified problem in DB .

解决方案

In both Oracle and SQL Server you can use the analytic/windowing functions RANK() or ROW_NUMBER() to achieve this:

select *
  from ( select a.*
              , rank() over ( partition by name, subject order by year desc ) rnk
           from ... a
                )
 where rnk = 1

RANK() will return 1 for every row that is the newest per name and subject, ROW_NUMBER() will return a random row.

In Oracle alone you can use KEEP to give you the same result:

select name, subject, max(year) as year
     , max(qualified) keep (dense_rank first order by year desc) as qualified
  from ...
 group by name, subject

这篇关于HAVING - GROUP BY获得最新的纪录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！