SQL 为每个 ID 获取最大值的简单查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/755918/
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
Simple Query to Grab Max Value for each ID
提问by Nick Sinas
OK I have a table like this:
好的，我有一张这样的表：
ID Signal Station OwnerID
111 -120 Home 1
111 -130 Car 1
111 -135 Work 2
222 -98 Home 2
222 -95 Work 1
222 -103 Work 2
This is all for the same day. I just need the Query to return the max signal for each ID:
这一切都是为了同一天。我只需要查询返回每个 ID 的最大信号：
ID Signal Station OwnerID
111 -120 Home 1
222 -95 Work 1
I tried using MAX() and the aggregation messes up with the Station and OwnerID being different for each record. Do I need to do a JOIN?
我尝试使用 MAX() 并且聚合混乱，因为每个记录的 Station 和 OwnerID 都不同。我需要加入吗？
回答by Andomar
Something like this? Join your table with itself, and exclude the rows for which a higher signal was found.
像这样的东西？将您的表与自身连接起来，并排除发现更高信号的行。
select cur.id, cur.signal, cur.station, cur.ownerid
from yourtable cur
where not exists (
select *
from yourtable high
where high.id = cur.id
and high.signal > cur.signal
)
This would list one row for each highest signal, so there might be multiple rows per id.
这将为每个最高信号列出一行，因此每个 id 可能有多行。
回答by bobince
You are doing a group-wise maximum/minimum operation. This is a common trap: it feels like something that should be easy to do, but in SQL it aggravatingly isn't.
您正在执行分组最大/最小操作。这是一个常见的陷阱：感觉应该很容易做到，但在 SQL 中却并非如此。
There are a number of approaches (both standard ANSI and vendor-specific) to this problem, most of which are sub-optimal in many situations. Some will give you multiple rows when more than one row shares the same maximum/minimum value; some won't. Some work well on tables with a small number of groups; others are more efficient for a larger number of groups with smaller rows per group.
有许多方法（标准 ANSI 和供应商特定的）可以解决这个问题，其中大多数在许多情况下都不是最佳的。当不止一行共享相同的最大值/最小值时，有些会给你多行；有些不会。有些在具有少量组的表上工作得很好；对于每组较小的行数较多的组，其他方法更有效。
Here's a discussionof some of the common ones (MySQL-biased but generally applicable). Personally, if I know there are no multiple maxima (or don't care about getting them) I often tend towards the null-left-self-join method, which I'll post as no-one else has yet:
这是对一些常见问题的讨论（偏向于 MySQL，但普遍适用）。就我个人而言，如果我知道没有多个最大值（或者不关心获得它们），我经常倾向于使用 null-left-self-join 方法，我将发布，因为其他人还没有：
SELECT reading.ID, reading.Signal, reading.Station, reading.OwnerID
FROM readings AS reading
LEFT JOIN readings AS highersignal
ON highersignal.ID=reading.ID AND highersignal.Signal>reading.Signal
WHERE highersignal.ID IS NULL;
回答by Jonathan Leffler
In classic SQL-92 (not using the OLAP operations used by Quassnoi), then you can use:
在经典的 SQL-92（不使用 Quassnoi 使用的 OLAP 操作）中，您可以使用：
SELECT g.ID, g.MaxSignal, t.Station, t.OwnerID
FROM (SELECT id, MAX(Signal) AS MaxSignal
FROM t
GROUP BY id) AS g
JOIN t ON g.id = t.id AND g.MaxSignal = t.Signal;
(Unchecked syntax; assumes your table is 't'.)
（未经检查的语法；假设您的表是 't'。）
The sub-query in the FROM clause identifies the maximum signal value for each id; the join combines that with the corresponding data row from the main table.
FROM 子句中的子查询标识了每个 id 的最大信号值；联接将其与主表中的相应数据行组合在一起。
NB: if there are several entries for a specific ID that all have the same signal strength and that strength is the MAX(), then you will get several output rows for that ID.
注意：如果特定 ID 的多个条目都具有相同的信号强度并且该强度是 MAX()，那么您将获得该 ID 的多个输出行。
Tested against IBM Informix Dynamic Server 11.50.FC3 running on Solaris 10:
针对在 Solaris 10 上运行的 IBM Informix Dynamic Server 11.50.FC3 进行测试：
+ CREATE TEMP TABLE signal_info
(
id INTEGER NOT NULL,
signal INTEGER NOT NULL,
station CHAR(5) NOT NULL,
ownerid INTEGER NOT NULL
);
+ INSERT INTO signal_info VALUES(111, -120, 'Home', 1);
+ INSERT INTO signal_info VALUES(111, -130, 'Car' , 1);
+ INSERT INTO signal_info VALUES(111, -135, 'Work', 2);
+ INSERT INTO signal_info VALUES(222, -98 , 'Home', 2);
+ INSERT INTO signal_info VALUES(222, -95 , 'Work', 1);
+ INSERT INTO signal_info VALUES(222, -103, 'Work', 2);
+ SELECT g.ID, g.MaxSignal, t.Station, t.OwnerID
FROM (SELECT id, MAX(Signal) AS MaxSignal
FROM signal_info
GROUP BY id) AS g
JOIN signal_info AS t ON g.id = t.id AND g.MaxSignal = t.Signal;
111 -120 Home 1
222 -95 Work 1
I named the table Signal_Info for this test - but it seems to produce the right answer. This only shows that there is at least one DBMS that supports the notation. However, I am a little surprised that MS SQL Server does not - which version are you using?
我为此测试命名了表 Signal_Info - 但它似乎产生了正确的答案。这仅表明至少有一个 DBMS 支持该表示法。但是，我有点惊讶 MS SQL Server 没有 - 您使用的是哪个版本？
It never ceases to surprise me how often SQL questions are submitted without table names.
在没有表名的情况下提交 SQL 问题的频率总是让我感到惊讶。
回答by han
with tab(id, sig, sta, oid) as
(
select 111 as id, -120 as signal, 'Home' as station, 1 as ownerId union all
select 111, -130, 'Car', 1 union all
select 111, -135, 'Work', 2 union all
select 222, -98, 'Home', 2 union all
select 222, -95, 'Work', 1 union all
select 222, -103, 'Work', 2
) ,
tabG(id, maxS) as
(
select id, max(sig) as sig from tab group by id
)
select g.*, p.* from tabG g
cross apply ( select top(1) * from tab t where t.id=g.id order by t.sig desc ) p
回答by minhas23
We can do using self join
我们可以使用 self join
SELECT T1.ID,T1.Signal,T2.Station,T2.OwnerID
FROM (select ID,max(Signal) as Signal from mytable group by ID) T1
LEFT JOIN mytable T2
ON T1.ID=T2.ID and T1.Signal=T2.Signal;
Or you can also use the following query
或者您也可以使用以下查询
SELECT t0.ID,t0.Signal,t0.Station,t0.OwnerID
FROM mytable t0
LEFT JOIN mytable t1 ON t0.ID=t1.ID AND t1.Signal>t0.Signal
WHERE t1.ID IS NULL;
回答by Quassnoi
WITH q AS
(
SELECT c.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY signal DESC) rn
FROM mytable
)
SELECT *
FROM q
WHERE rn = 1
This will return one row even if there are duplicates of MAX(signal)for a given ID.
即使MAX(signal)给定的存在重复项，这也将返回一行ID。
Having an index on (id, signal)will greatly improve this query.
建立索引(id, signal)将大大改善此查询。
回答by HLGEM
select a.id, b.signal, a.station, a.owner from
mytable a
join
(SELECT ID, MAX(Signal) as Signal FROM mytable GROUP BY ID) b
on a.id = b.id AND a.Signal = b.Signal
回答by Mosharaf Hossain
SELECT * FROM StatusTable
WHERE Signal IN (
SELECT A.maxSignal FROM
(
SELECT ID, MAX(Signal) AS maxSignal
FROM StatusTable
GROUP BY ID
) AS A
);
回答by Tutu Kumari
select id, max_signal, owner, ownerId FROM ( select * , rank() over(partition by id order by signal desc) as max_signal from table ) where max_signal = 1;
select id, max_signal, owner, ownerId FROM ( select * , rank() over(partition by id order by signal desc) as max_signal from table ) where max_signal = 1;
