SQL 如何在不使用子查询的情况下仅选择具有最大序列的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13153819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to select only row with max sequence without using a subquery?
提问by jenswirf
I'm trying to select only the row with the highest seq for each ID
我试图只为每个 ID 选择具有最高 seq 的行
ID | Seq | Age
-------------------
A 1 20
A 2 30
B 1 25
B 2 32
B 3 44
B 4 48
C 1 11
This appears to work
这似乎有效
SELECT ID, Age
FROM Persons a
WHERE Seq = (SELECT MAX(Seq) FROM Persons b WHERE a.ID = b.ID)
But is this the best way, the only way? I don't like using subqueries if I don't have to and I recall you can use something but I forget what it is. Any idea?
但这是最好的方法，唯一的方法吗？如果我不需要，我不喜欢使用子查询，我记得你可以使用一些东西，但我忘记了它是什么。任何的想法？
采纳答案by Tim Schmelter
Assuming SQL-Server ( >= 2005) or Oracle (10g?):
假设 SQL-Server (>= 2005) 或 Oracle (10g?)：
WITH CTE AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Seq DESC) AS RN
, ID, Age
FROM
Persons
)
SELECT ID, Age
FROM CTE
WHERE RN = 1
ROW_NUMBERreturns the sequential number of a row within a partition of a result set.
ROW_NUMBER返回结果集分区内行的序列号。
Edit: works also in Oracle as you can see here: http://sqlfiddle.com/#!4/b7e79/2/0
编辑：也可以在 Oracle 中使用，如您所见：http: //sqlfiddle.com/#!4/b7e79/2/0
回答by podiluska
In general, you neeed to use windowing or ranking functions - Rank(), Row_number(), etc.
在一般情况下，你要neeed使用窗口或排名函数- Rank()，Row_number()等等。
select *
from
(
select *, row_number() over (partition by id order by age desc) rn
from yourtable
) v
where rn = 1
This will work in SQL Server 2005+ - in oracle you may need to specify the field names explicitly, instead of the *
这将适用于 SQL Server 2005+ - 在 oracle 中，您可能需要明确指定字段名称，而不是 *
回答by GarethD
Just in case you use an RDBMS that doesn't support window functions, you can use:
万一您使用不支持窗口函数的 RDBMS，您可以使用：
SELECT Persons.ID, Persons.Age, Persons.Seq
FROM Persons
INNER JOIN
( SELECT Persons.ID, MAX(Seq) AS Seq
FROM Persons
GROUP BY Persons.ID
) MaxP
ON MaxP.ID = Persons.ID
AND MaxP.Seq = Persons.Seq
It still involves a subquery, but I don't see a way of doing this without one, nor do I really understand why you would want to avoid them.
它仍然涉及一个子查询，但我看不到没有子查询的方法，我也不太明白你为什么要避免它们。
