SQL 如何使用“Partition By”或“Max”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6198320/
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 use "Partition By" or "Max"?
提问by Stef Heyenrath
I've the following table (my_data):
我有下表（my_data）：
year | X | Y
-----+-----+-----
2010 | A | 10
2011 | A | 20
2011 | B | 99
2009 | C | 30
2010 | C | 40
what is the best / smallest SQL statement to retrieve only the data related to the highest year and grouped by 'X' , like this:
仅检索与最高年份相关并按 'X' 分组的数据的最佳/最小 SQL 语句是什么，如下所示：
year | X | Y
-----+-----+-----
2011 | A | 20
2011 | B | 99
2010 | C | 40
Note that this result table will be used in a join.
请注意，此结果表将用于连接。
回答by schurik
select year, x,y
from (
select year, x, y, max(year) over(partition by x) max_year
from my data
)
where year = max_year
回答by Frank Schmitt
select * from (
select year, x, y, row_number() over (partition by x order by year desc ) rn
from my_data
) where rn = 1
回答by Benoit
You could also be portable and use an OUTER JOIN :
您也可以是可移植的并使用 OUTER JOIN ：
select t1.year, t1.x, t1.y
from my_data t1
left join my_data t2
on t2.x = t1.x
and t2.year > t1.year
where t2.x is null
回答by Gary Myers
It's a lot simpler than the other solutions:
它比其他解决方案简单得多：
SELECT x, max(year), MAX(y) KEEP (DENSE_RANK FIRST ORDER BY year DESC)
FROM table
GROUP BY x
回答by AleksandarT
Gary Myers, your solution does not work, if, for example, for value A, year is smaller than 2010 and that year has maximum value. (FOR example, if row 2005,A,50 existed) In order to get correct solution, use the following. (which just swaps values)
加里迈尔斯，您的解决方案不起作用，例如，对于值 A，年份小于 2010 年并且该年份具有最大值。（例如，如果第 2005,A,50 行存在）为了获得正确的解决方案，请使用以下内容。（这只是交换值）
SELECT x, max(y), MAX(year) KEEP (DENSE_RANK FIRST ORDER BY y DESC)
FROM test
GROUP BY x
回答by Artyom
You can use common table expression (CTE), works also with duplicated rows (if required) Execution plan is the same, more or less
您可以使用公用表表达式 (CTE)，也适用于重复行（如果需要）执行计划相同，或多或少
;With my_data_cte as (
SELECT [year], x,y,ROW_NUMBER() OVER (
PARTITION BY x
ORDER BY [year] desc) as rn
FROM [dbo].[my_data])
select [year], x,y from my_data_cte
where rn = 1
回答by Yordan Georgiev
-- I had a slightly different case and just wandering why this one should't work
SELECT my_data.x , my_data.y , my_data1.max_year
FROM my_data
INNER JOIN
(
SELECT x , max (year ) as max_year
FROM my_data
-- WHERE 1=1
-- AND FILTER1=VALUE1
GROUP BY my_data.x
) my_data1
ON ( my_data.x = my_data1.x )
回答by minglotus
select year, x, y
from my_data stable
where stable.year = (select max(year)
from my_data tables
where tables.x = stable.x);
回答by Amardeep Bhagat
this can also be the solution
这也可以是解决方案
select greatest( (e),(g),(c),(a),(b) ) as latestdate from abc
从 abc 中选择最大（（e）、（g）、（c）、（a）、（b））作为最新日期
回答by Karl
The simplest is
最简单的是
Select *
from table
where year = (select max(year) from table)
It will possibly cause a table scan unless there is an index on year. But with an index should be performant
除非年份有索引，否则它可能会导致表扫描。但是使用索引应该是高性能的
