mysql - 简单的MySQL自连接查询，想要获取每个重复条目的ID列

Question

SQL fiddle -> http://sqlfiddle.com/#!2/28938/9

我有一个这样的表:

+------+----------+--------+
|  id  | group_id | letter |
+------+----------+--------+
|  1   | 44       | a      |
|  2   | 55       | a      |
|  3   | 44       | b      |
|  4   | 55       | c      |
|  5   | 44       | c      |
|  6   | 55       | d      |
+------+----------+--------+

我想选择任何计数为 2 或更多的字母以及包含该字母的 group_id。这是我正在处理的查询:

SELECT b.id AS idx, a.id AS idy, a.letter FROM (
  SELECT id, letter, COUNT(1) AS count
  FROM temp WHERE group_id = 55
  OR group_id = 44
  GROUP BY letter
) AS a
JOIN temp AS b ON a.letter = b.letter
WHERE a.count > 1
AND group_id = 55

这是我想要的结果:

+-----+-----+--------+
| idx | idy | letter |
+-----+-----+--------+
| 2   | 1   | a      |
| 4   | 5   | c      |
+-----+-----+--------+

但不幸的是，这就是我得到的

+-----+-----+--------+
| idx | idy | letter |
+-----+-----+--------+
| 2   | 1   | a      |
| 4   | 4   | c      |
+-----+-----+--------+

如何保证 ID 位于正确的列中？

谢谢!

Answer 1

这并不是您想要的布局，但它确实返回 4 个字段。您上面的查询不起作用，因为您的内部查询不会产生 id #5。下面修改后的查询可以做到这一点，但感觉很hacky。

SELECT
      MAX(IF (b.group_id = 55, b.id, -1)) as idx,
      MAX(IF (b.group_id = 44, b.id, -1)) as idy,
      b.letter
FROM 
  temp b

INNER JOIN (
  SELECT 
      letter, group_id, COUNT(1) AS count
    FROM temp WHERE group_id = 55
    OR group_id = 44
    GROUP BY letter
    HAVING count > 1
  ) a ON (b.letter = a.letter)

GROUP BY letter