每个ID获取最大值的简单查询

Question

好的，我有这样一张表：

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

这都是同一天的事情。我只需要查询每个ID的最大信号：

ID    Signal    Station    OwnerID
111   -120      Home        1
222   -95       Work        1

我尝试使用MAX()函数，但由于每个记录的Station和OwnerID不同，聚合会出现问题。我需要进行JOIN操作吗？

- Nick Sinas

---

你使用的是哪个版本的SQL Server？ - Jonathan Leffler

Answer 1

像这样吗？将表格与自身连接，并排除发现更高信号的行。

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
)

这将列出每个最高信号的一行，因此可能会有多个行与同一个ID相关。

- Andomar

---

如果多个站点的信号相同，则此代码确实会返回重复项。 - Nick Sinas

编辑后您可以获得每个信号的多行，但不会重复。如果您只想从具有最高信号的行中选择任意一行，请使用Quassnoi的答案。 - Andomar

是的，我认为这个可以工作。我需要检查数据。但非常感谢。 - Nick Sinas

易于理解，即使十年后仍能很好地工作，非常感谢！ - Elliott Addi

复杂度怎么样呢？这不是O(N^2)吗？ - user1111929

Answer 2

您正在执行分组最大/最小操作。这是一个常见的陷阱：感觉这应该很容易做到，但在SQL中却令人恼火。

有许多方法（标准ANSI和供应商特定的），可以解决这个问题，但大多数情况下都不太理想。一些方法会在多行共享相同的最大/最小值时给您多个行；而有些方法则不会。在具有少量组的表上运作良好的方法，在具有更多组且每组行数较少的情况下效率更高。

这里讨论了一些常见的方法（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;

- bobince

---

使用“reading”和“highersignal”别名使查询的理解变得轻松！谢谢。 - Sabuncu

Answer 3

在经典的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;

(未经检查的语法; 假设您的表为“t”.)

FROM子查询识别每个ID的最大信号值; 连接将其与主表中相应的数据行组合。

注意: 如果特定ID有多个条目，所有条目都具有相同的信号强度，并且该强度是MAX()，则会获得该ID的几个输出行。

---

针对运行在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

我为这个测试命名了表Signal_Info，但它似乎产生了正确的答案。这只是表明至少有一个DBMS支持这种表示法。然而，我有点惊讶的是MS SQL Server不支持 - 你使用的是哪个版本？

---

SQL问题经常被提交，但没有表名，这总是让我感到惊讶。

- Jonathan Leffler

---

我遇到了一个“FROM子句中的语法错误”错误，并且它指向了JOIN。 - Nick Sinas

Answer 4

WITH q AS
         (
         SELECT  c.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY signal DESC) rn
         FROM    mytable
         )
SELECT   *
FROM     q
WHERE    rn = 1

即使对于给定的ID，存在多个MAX(signal)的重复值，此命令也将返回一行。

在(id，signal)上创建索引将极大地改善此查询。

- Quassnoi

---

最好使用聚合和连接方法而不是创建列。优化器可以将其作为整体进行评估：这里的计算列需要先计算，因此很可能需要在某个地方进行暂存。 - gbn

如果您在此列上有索引（应该有），则连接将不太有效率。 - Quassnoi

我知道，但使用索引对于 SQL Server 2005 更有效。 - Quassnoi

好的，我刚刚测试了13000行和300000行的表格。IO较少，对于较小的表格更好。 - gbn

你的。较少的逻辑读取，较少的扫描。然而，对于较大的表来说，批处理的百分比显示得更糟糕...但我更倾向于IO统计。不过我没有检查CPU。 - gbn

Answer 5

使用tab(id, sig, sta, oid)作为表名，其中
id: 记录的唯一标识符
sig: 信号值，是一个整数
sta: 状态值，表示地点的字符串
oid: 拥有者的唯一标识符
查询该表中每个记录的最大信号值和对应的记录信息，结果中包含每个记录的所有信息以及其最大信号值，可以使用以下查询语句：
with tabG(id, maxSig) as
(
   select id, max(sig) as maxSig 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

- han

Answer 6

我们可以使用自连接。

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;

或者您也可以使用以下查询。

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;

- minhas23

Answer 7

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 

- HLGEM

---

@thegreekness：在表别名之间是否需要显式包含AS关键字？mytable AS a JOIN (SELECT ...) AS b? 你不应该这样做，但是... - Jonathan Leffler

我刚意识到 - ON条件必须同时指定一个连接信号。 - Jonathan Leffler

这可能是批处理中唯一有效的解决方案。但它需要一个复合索引（id，signal）。 - Rick James

Answer 8

选择 id， max_signal， owner， ownerId 从 ( 选择*，rank() over(partition by id order by signal desc) as max_signal from table ) 其中 max_signal = 1;

- Tutu Kumari

Answer 9

从StatusTable中选择所有数据，其中Signal在maxSignal的子查询结果中出现过：SELECT A.maxSignal FROM(SELECT ID, MAX(Signal) AS maxSignal FROM StatusTable GROUP BY ID) AS A

- Mosharaf Hossain