数据,当字段A相同时,取字段B数值大的这一条数据
A | B | C |
1 | 2 | 3 |
1 | 1 | 4 |
2 | 2 | 3 |
2 | 3 | 4 |
期望结果
A | B | C |
1 | 2 | 3 |
2 | 3 | 4 |
Oracle
SELECT A, B, C
FROM (
SELECT A, B, C,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B DESC) AS rn
FROM 表名
)
WHERE rn = 1;
MySql
SELECT t1.A, t1.B, t1.C
FROM table_name t1
JOIN (
SELECT A, MAX(C) AS max_C
FROM 表名
GROUP BY A
) t2 ON t1.A = t2.A AND t1.C = t2.max_C;