SQL SQL查询以选择具有最小值的不同行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15291506/
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
SQL query to select distinct row with minimum value
提问by balaji
I want an SQL statement to get the row with a minimum value.
我想要一个 SQL 语句来获取具有最小值的行。
Consider this table:
考虑这个表：
id game point
1 x 5
1 z 4
2 y 6
3 x 2
3 y 5
3 z 8
How do I select the ids that have the minimum value in the pointcolumn, grouped by game? Like the following:
如何选择列中具有最小值的 id point，按游戏分组？像下面这样：
id game point
1 z 4
2 y 5
3 x 2
回答by Ken Clark
Use:
用：
SELECT tbl.*
FROM TableName tbl
INNER JOIN
(
SELECT Id, MIN(Point) MinPoint
FROM TableName
GROUP BY Id
) tbl1
ON tbl1.id = tbl.id
WHERE tbl1.MinPoint = tbl.Point
回答by Shiroy
This is another way of doing the same thing, which would allow you to do interesting things like select the top 5 winning games, etc.
这是做同样事情的另一种方式，它可以让你做一些有趣的事情，比如选择前 5 名获胜的游戏等。
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Point) as RowNum, *
FROM Table
) X
WHERE RowNum = 1
You can now correctly get the actual row that was identified as the one with the lowest score and you can modify the ordering function to use multiple criteria, such as "Show me the earliest game which had the smallest score", etc.
您现在可以正确获取被识别为最低分数的实际行，并且您可以修改排序函数以使用多个条件，例如“显示具有最低分数的最早游戏”等。
回答by Aspirant
This will work
这将工作
select * from table
where (id,point) IN (select id,min(point) from table group by id);
回答by a_horse_with_no_name
As this is tagged with sqlonly, the following is using ANSI SQL and a window function:
由于sql仅标记为，因此以下使用 ANSI SQL 和窗口函数：
select id, game, point
from (
select id, game, point,
row_number() over (partition by game order by point) as rn
from games
) t
where rn = 1;
回答by samthebrand
Ken Clark's answerdidn't work in my case. It might not work in yours either. If not, try this:
肯克拉克的回答在我的情况下不起作用。它可能也不适用于您的。如果没有，试试这个：
SELECT *
from table T
INNER JOIN
(
select id, MIN(point) MinPoint
from table T
group by AccountId
) NewT on T.id = NewT.id and T.point = NewT.MinPoint
ORDER BY game desc
回答by Tshultrim Dorji
SELECT * from room
INNER JOIN
(
select DISTINCT hotelNo, MIN(price) MinPrice
from room
Group by hotelNo
) NewT
on room.hotelNo = NewT.hotelNo and room.price = NewT.MinPrice;
回答by The Conspiracy
This alternative approach uses SQL Server's OUTER APPLYclause. This way, it
这种替代方法使用 SQL Server 的OUTER APPLY子句。这样，它
- creates the distinct list of games, and
- fetches and outputs the record with the lowest point number for that game.
- 创建不同的游戏列表，以及
- 获取并输出该游戏具有最低点数的记录。
The OUTER APPLYclause can be imagined as a LEFT JOIN, but with the advantage that you can use values of the main query as parameters in the subquery(here: game).
该OUTER APPLY子句可以想象为 a LEFT JOIN，但其优点是您可以使用主查询的值作为子查询中的参数（此处：游戏）。
SELECT colMinPointID
FROM (
SELECT game
FROM table
GROUP BY game
) As rstOuter
OUTER APPLY (
SELECT TOP 1 id As colMinPointID
FROM table As rstInner
WHERE rstInner.game = rstOuter.game
ORDER BY points
) AS rstMinPoints
回答by Mohamed Ashraf
SELECT DISTINCT
FIRST_VALUE(ID) OVER (Partition by Game ORDER BY Point) AS ID,
Game,
FIRST_VALUE(Point) OVER (Partition by Game ORDER BY Point) AS Point
FROM #T
回答by www
Try:
尝试：
select id, game, min(point) from t
group by id
