如何在 SQL 中查找最大值及其关联的字段值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6196409/
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 find max value and its associated field values in SQL?
提问by Steve
Say I have a list of student names and their marks. I want to find out the highest mark and the student, how can I write one select statement to do that?
假设我有一个学生姓名及其分数的列表。我想找出最高分和学生，我该如何写一个选择语句来做到这一点？
回答by paxdiablo
Assuming you mean marks rather than remarks, use:
假设您的意思是标记而不是备注，请使用：
select name, mark
from students
where mark = (
select max(mark)
from students
)
This will generally result in a fairly efficient query. The subquery should be executed once only (unless your DBMS is brain-dead) and the result fed into the second query. You may want to ensure that you have an index on the markcolumn.
这通常会导致相当有效的查询。子查询应该只执行一次（除非您的 DBMS 是脑死亡），并将结果输入到第二个查询中。您可能希望确保在该mark列上有一个索引。
回答by Sam Dufel
If you don't want to use a subquery:
如果您不想使用子查询：
SELECT name, remark
FROM students
ORDER BY remark DESC
LIMIT 1
回答by Algorithmist
select name, remarks
from student
where remarks =(select max(remarks) from student)
回答by Andrew Lazarus
If you are using a database that supports windowing,
如果您使用的是支持窗口化的数据库，
SELECT name, mark FROM
(SELECT name, mark, rank() AS rk
FROM student_marks OVER (ORDER BY mark DESC)
) AS subqry
WHERE subqry.rk=1;
This probably does notrun as fast as the mark=(SELECT MAX(mark)...style query, but it would be worth checking out.
这大概也不会那么快跑mark=(SELECT MAX(mark)...风格的查询，但它是值得一试。
回答by Andriy M
In SQL Server:
在 SQL Server 中：
SELECT TOP 1 WITH TIES *
FROM Students
ORDER BY Mark DESC
This will return all the students that have the highest mark, whether there is just one of them or more than one. If you want only one row, drop the WITH TIESspecifier. (But the actual row is not guaranteed to be always the same then.)
这将返回所有得分最高的学生，无论他们只有一个还是不止一个。如果您只需要一行，请删除说明WITH TIES符。（但不能保证实际的行总是相同的。）
回答by Maadh
You can create view and join it with original table:
您可以创建视图并将其与原始表连接：
V1
select id , Max(columName)
from t1
group by id
select * from t1
where t1.id = V1.id and t1.columName = V1.columName
this is right if you need Max Values with related info
如果您需要具有相关信息的最大值，这是正确的
回答by sisdog
I recently had a need for something "kind of similar" to this post and wanted to share a technique. Say you have an Order and OrderDetail table, and you want to return info from the Order table along with the product name associated with the highest priced detail row. Here's a way to pull that off without subtables, RANK, etc.. The key is to create and aggregate that combined the key and value from the detailed table and then just max on that and substring out the value you want.
我最近需要一些与这篇文章“类似”的东西，并想分享一种技术。假设您有一个 Order 和 OrderDetail 表，并且您想要从 Order 表中返回信息以及与价格最高的详细信息行关联的产品名称。这是一种在没有子表、RANK 等的情况下实现这一目标的方法。关键是创建和聚合组合明细表中的键和值，然后在该表上最大化并子串出您想要的值。
create table CustOrder(ID int)
create table CustOrderDetail(OrderID int, Price money, ProdName varchar(20))
insert into CustOrder(ID) values(1)
insert into CustOrderDetail(OrderID,Price,ProdName) values(1,10,'AAA')
insert into CustOrderDetail(OrderID,Price,ProdName) values(1,50,'BBB')
insert into CustOrderDetail(OrderID,Price,ProdName) values(1,10,'CCC')
select
o.ID,
JoinAggregate=max(convert(varchar,od.price)+'*'+od.prodName),
maxProd=
SUBSTRING(
max(convert(varchar,od.price)+'*'+od.prodName)
,CHARINDEX('*',max(convert(varchar,od.price)+'*'+convert(varchar,od.prodName))
)+1,9999)
from
CustOrder o
inner join CustOrderDetail od on od.orderID = o.ID
group by
o.ID
