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 |
+------+----------+--------+
我想选择任何计数为两个或更多的字母加上两个group_ids具有该字母的字母。这是我正在处理的查询:
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 位于正确的列中?
谢谢!
这不是您想要的布局,但它确实返回了 4 个字段。上面的查询不起作用,因为您的内部查询不会导致 id #5。下面的修改查询可以做到,但感觉很黑客。
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