mysql查找出现出现两次的项半路遇到迪威贵宾会I8Io883oo

看表：

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)

       酱紫。