MySQL-SQL选择查询有两个表，分别使用where、count和have

Question

有两个表：client和contract。

客户端表：

client_code INT pk
status      VARCHAR

一个客户可以有一个或多个合同。客户端有一个状态列，用于指定它是否具有有效的合约-值为"活动"或"非活动"。合同是为活动状态的客户端指定的。

合同表：

contract_code INT pk
client_code   INT pk
end_date      DATE

合同有终止日期。今天之前的合同结束日期是过期的合同。

要求：报表要求所有具有合同的活动客户端，但所有(而不是某些(合同都已过期。一些示例数据如下所示：

客户端数据：

client_code status
----------------------------------
1           active
2           inactive
3           active
4           active

合同数据：

contract_code   client_code   end_date
-------------------------------------------------------------
11              1             08-12-2018    
12              1             09-12-2018
13              1             10-12-2018
31              3             11-31-2018
32              3             10-30-2018
41              4             01-31-2019
42              4             12-31-2018

预期结果：

client_code
-------------
1

结果：此客户端(client_code=1(具有所有过期日期的合同：08-12-2018、09-12-2018和10-12-2018。

我需要一些帮助来编写SQL查询以获得此结果。我不确定我必须使用什么结构——人们可以指出我可以尝试什么。数据库为MySQL 5.5。

Answer 1

一种方法使用聚合。我们可以将client和contract表连接在一起，然后按客户端聚合，检查对于活动客户端，是否存在将来发生的合同结束日期。

SELECT
c.client_code
FROM client c
INNER JOIN contract co
ON c.client_code = co.client_code
WHERE
c.status = 'active'
GROUP BY
c.client_code
HAVING
SUM(CASE WHEN co.end_date > CURDATE() THEN 1 ELSE 0 END) = 0;

演示

注意：我假设您的日期显示为M-D-Y格式只是由于特定的格式，而end_date实际上是一个合适的日期列。如果您将日期存储为文本，那么我们可能需要先致电STR_TO_DATE将其转换为日期。

Answer 2

这就是您想要的吗？

select clients.client_code
from clients
join contracts
on contracts.client_code=clients.client_code
where status='active'
group by clients.client_code
having min(end_date)>curdate()