mysql - Mysql 列出重复次数超过一定值的条目

Question

我有一个这样的员工表 --->

+------+------------------+------+------------+--------+
| EC   | Name             | Code | Dob        | Salary |  
+------+------------------+------+------------+--------+
| 2001 | ROBBIE KEANE     | VSS1 | 1990-05-16 |  18000 |
| 2002 | ANSUMAN BANERJEE | VSS1 | 1985-05-21 |  18000 |
| 2003 | OMAR GONZALEZ    | SACP | 1989-04-16 |  20000 |
| 2004 | ALAN GORDON      | IALO | 1989-05-03 |  20000 |
| 2005 | ROBBIE KEANE     | IALO | 1988-01-16 |  18000 |
| 2006 | CHANDLER HOFFMAN | BBDP | 1988-07-17 |  22000 |
| 2007 | PAUL POGBA       | BHSM | 1990-08-16 |  18000 |
| 2008 | SHINJI KAGAWA    | LPDC | 1991-01-20 |  18000 |
+------+------------------+------+------------+--------+

现在我想列出那些分配给它们的人数少于指定人数的代码(例如 VSS1)(比如少于 2 个)，我该怎么做，请帮忙。

到目前为止我的查询是-->

SELECT Code,count(*) as 'Number of Staff' from STAFF where Code IN (SELECT Code from STAFF GROUP BY CODE LIMIT 2);

但这不起作用。

Answer 1

您可以使用 HAVING 子句过滤每个 Code 组的行数:

SELECT Code
     , COUNT(*)
FROM STAFF 
GROUP BY Code
HAVING COUNT(*) < 2