如何创建获得不同条目的sql脚本？

Question

​

​

我有一个上面的图片，我想保留项目2和项目4的最大计数和名称重复，但我需要保留ID列。如何编写SQL脚本并获得如下结果？

ID  Name    Count
2   A   5
4   B   5

Answer 1

很确定这就是你想要的：

SELECT id, name, count
  FROM my_table mt1
 WHERE count = (SELECT MAX(count) FROM my_table mt2 WHERE mt1.name = mt2.name);

编辑：，但是，就像克里斯建议的那样，它确实有重复的：

SELECT MAX(id), name, count
  FROM my_table mt1
 WHERE count = (SELECT MAX(count) FROM my_table mt2 WHERE mt1.name = mt2.name)
GROUP BY name, count;

产出如下：

+------+------+-------+
| id   | name | count |
+------+------+-------+
|    2 | A    |     5 |
|    4 | B    |     5 |
+------+------+-------+

Answer 2

SELECT ID, Name, Count
FROM myTable
WHERE (SELECT COUNT(*) FROM myTable t2
  WHERE t2.Name = myTable.Name
  AND (t2.Count > myTable.Count
  OR (t2.Count = myTable.Count AND t2.ID > myTable.ID))) = 0;