看表：
mysql> select * from x;
---- --- ---
| id | a | b |
---- --- ---
| 1 | 1 | 2 |
| 2 | 5 | 3 |
| 3 | 1 | 4 |
| 4 | 1 | 5 |
| 5 | 5 | 1 |
| 6 | 6 | 1 |
---- --- ---
用group by:
mysql> select a, count(*) from x group by a;
--- ----------
| a | count(*) |
--- ----------
| 1 | 3 |
| 5 | 2 |
| 6 | 1 |
--- ----------
3 rows in set (0.00 sec)
然后再筛选：
mysql> select a, count(*) as c from x group by a where c = 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where c = 2' at line 1
mysql>
应该用having:
mysql> select a, count(*) as c from x group by a having c = 2;
--- ---
| a | c |
--- ---
| 5 | 2 |
--- ---
1 row in set (0.00 sec)
mysql>
或者：
mysql> select * from (select a, count(*) as c from x group by a ) where c = 2;
ERROR 1248 (42000): Every derived table must have its own alias
mysql>
改为：
mysql> select * from (select a, count(*) as c from x group by a ) as b where c = 2;
--- ---
| a | c |
--- ---
| 5 | 2 |
--- ---
1 row in set (0.00 sec)
或者：
mysql> select * from (select a, count(*) as c from x group by a ) as b where b.c = 2;
--- ---
| a | c |
--- ---
| 5 | 2 |
--- ---
1 row in set (0.00 sec)
酱紫。
