mysql查找出现出现两次的项------半路遇到ERROR 1248 (42000): Every derived table must have its own alias

     看表：

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)

       酱紫。