MySQL检查同一值在另一个表中是否存在三次

Question

我有两个MySQL表：

API_Keys
+----+---------------+--------------+
| id | key_val       | Expiration |
+----+---------------+--------------+
| 1  | 111111111111  | 2018-11-13 |
+----+---------------+--------------+
| 2  | 222222222222  | 2018-11-13 |
+----+---------------+--------------+
| 3  | 333333333333  | 2018-12-13 |
+----+---------------+--------------+
| 3  | 444444444444  | 2018-11-13 |

Used_Keys
+----+------+--------------+
| id | user | key_val      |
+----+------+--------------+
| 1  | john | 111111111111 |
+----+------+--------------+
| 2  | sami | 111111111111 |
+----+------+--------------+
| 3  | mary | 111111111111 |
+----+------+--------------+
| 4  | nina | 333333333333 |
+----+------+--------------+
| 5  | leon | 333333333333 |

每个key_val最多可分配给3个用户。所以我需要列出所有的API_Keys表数据，除了已经在Used_Keys表中分配了3次的key_vals。

因此，免费API_Keys的预期结果是：

API_Keys
+----+---------------+--------------+
| id | key_val       | Expiration |
+----+---------------+--------------+
| 1  | 222222222222  | 2018-11-13 |
+----+---------------+--------------+
| 2  | 333333333333  | 2018-12-13 |
+----+---------------+--------------+
| 3  | 444444444444  | 2018-11-13 |

如果您帮助我编写用于此目的的MySQL select查询，我会进行适当的处理。

Answer 1

select * 
from api_keys as a
left join (
    select key_val 
    from Used_Keys
    group by key_val 
    having count(*) >= 3
    ) as u on a.key_val = u.key_val 
where u.key_val IS NULL