sql - 在至少一行满足条件的情况下，如何返回所有行的值？

我试图返回id的所有行，其中一个或多个Num_Occurrence行>=10。
以下是原始数据的示例：

+------+-----------+----------------+
| ID   | YearMonth | Num_Occurrence |
+------+-----------+----------------+
| 1234 | 201601    | 7              |
+------+-----------+----------------+
| 1234 | 201602    | 8              |
+------+-----------+----------------+
| 1234 | 201603    | 12             |
+------+-----------+----------------+
| 1234 | 201604    | 9              |
+------+-----------+----------------+
| 9898 | 201601    | 9              |
+------+-----------+----------------+
| 9898 | 201602    | 8              |
+------+-----------+----------------+
| 9898 | 201603    | 9              |
+------+-----------+----------------+
| 9898 | 201604    | 6              |
+------+-----------+----------------+

这里是期望的输出：

+------+-----------+----------------+
| ID   | YearMonth | Num_Occurrence |
+------+-----------+----------------+
| 1234 | 201601    | 7              |
+------+-----------+----------------+
| 1234 | 201602    | 8              |
+------+-----------+----------------+
| 1234 | 201603    | 12             |
+------+-----------+----------------+
| 1234 | 201604    | 9              |
+------+-----------+----------------+

我知道以下方法不起作用：

SELECT *
FROM tbl
WHERE Num_Occurrence >= 10

因为这只会返回此行：

+------+-----------+----------------+
| ID   | YearMonth | Num_Occurrence |
+------+-----------+----------------+
| 1234 | 201603    | 12             |
+------+-----------+----------------+

如前所述，我需要返回任何ID的所有行，其中Num_Occurrence>=10。
谢谢！!

最佳答案

SELECT * FROM [tbl] t1
WHERE EXISTS (SELECT * FROM [tbl] t2
              WHERE t2.ID = t1.id
              AND t2.Num_Occurrence >= 10);

这里的“EXISTS”子句使用子查询查找Num_Occurrence>=10的所有行，然后将其与完整表进行比较，以获取具有匹配ID的所有行。