sql - 获取特定列中具有最高值的不同值

Question

如何在 SQL 中从下表中获取突出显示的行？ (根据最高版本的用户名突出显示不同的行)

如果您需要纯文本表格:

+----+-----------+---+
| 1  | John      | 1 |
+----+-----------+---+
| 2  | Brad      | 1 |
+----+-----------+---+
| 3  | Brad      | 3 |
+----+-----------+---+
| 4  | Brad      | 2 |
+----+-----------+---+
| 5  | Jenny     | 1 |
+----+-----------+---+
| 6  | Jenny     | 2 |
+----+-----------+---+
| 7  | Nick      | 4 |
+----+-----------+---+
| 8  | Nick      | 1 |
+----+-----------+---+
| 9  | Nick      | 3 |
+----+-----------+---+
| 10 | Nick      | 2 |
+----+-----------+---+
| 11 | Chris     | 1 |
+----+-----------+---+
| 12 | Nicole    | 2 |
+----+-----------+---+
| 13 | Nicole    | 1 |
+----+-----------+---+
| 14 | James     | 1 |
+----+-----------+---+
| 15 | Christine | 1 |
+----+-----------+---+

到目前为止我所拥有的是(适用于一个用户)

SELECT USER, VERSION 
FROM TABLE 
WHERE USER = 'Brad' 
AND VERSION  = (SELECT MAX(VERSION ) FROM TABLE WHERE USER= 'Brad')

Answer 1

SELECT USER, max(VERSION) VERSION
FROM TABLE GROUP BY USER;

如果您需要身份证，那么

SELECT ID, USER, VERSION FROM (
    SELECT ID, USER, VERSION, 
           RANK() OVER(PARTITION BY USER ORDER BY VERSION DESC) RNK 
    FROM TABLE
) WHERE RNK = 1;

如果你有

| 2  | Brad      | 5 |
+----+-----------+---+
| 3  | Brad      | 3 |
+----+-----------+---+
| 4  | Brad      | 5 |

带有 RANK 的查询为您提供两个用户

| 2  | Brad      | 5 |
+----+-----------+---+
| 4  | Brad      | 5 |

如果您只需要一行，请将 RANK() 替换为 ROW_NUMBER()

在您的查询中，您使用 AND VERSION = (SELECT MAX(VERSION) FROM TABLE WHERE USER= 'Brad') 这相当于 RANK()(所有具有最大 VERSION 的行)