SQL 查询 - 选择至少有两个重复值的行

Question

这是我的示例表:

|"id","singlesaleid","quantity"
-------------------------------------
| 1,   "00278e0f",    37
| 2,   "00278e0f",    38
| 3,   "002ebd2e",    37
| 4,   "00380783",    37
| 5,   "003b3c35",    37
| 6,   "003b3c35",    38
| 7,   "0042170b",    38
| 8,   "00421b89",    37
| 9,   "00421b89",    38
| 10,  "00587f02",    37
| 11,  "00799433",    38
| 12,  "00799433",    37

如何使选择仅选择和分组 singlesaleid 列中至少有两个重复值的行？

结果应该是:

|"id","singlesaleid","quantity"
-------------------------------------
| 1,   "00278e0f",      37
| 6,   "003b3c35",      38
| 9,   "00421b89",      38
| 12,  "00799433",      37

有人可以帮忙吗？

Answer 1

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  singlesaleid, MIN(quantity) min_val
            FROM    TableName
            GROUP   BY singlesaleid
            HAVING  COUNT(*) > 1
        ) b a.singlesaleid = b.singlesaleid AND
            a.quantity = b.Min_Val

SQLFiddle Demo