如何在一对多联接中获取 NULL 记录计数

Question

我有一个account和plan表，其中用户帐户在给定时间可以没有或单个/多个或不活动计划与之关联。

account表：

+---------+-------------+---+
|   ID    | account_id  | ..|
+---------+-------------+---+
|   1     |   111111    |   |
|   2     |   222222    |   |
|   3     |   333333    |   |
|   4     |   444444    |   |
+---------+-------------+---+

plan表：

+----+--------------------+----------------+-----------------+---------------------+---------------------+
| id |     account_id     | attribute_key  | attribute_value | start_date          | end_date            |
+----+--------------------+----------------+-----------------+---------------------+---------------------+
|  1 |             111111 |      RPC       | AB              | 2011-10-01 00:00:00 | NULL                |
|  2 |             111111 |      RPC       | CND             | 2011-10-01 00:00:00 | NULL                |
|  3 |             222222 |      RPC       | IA              | 2011-10-01 00:00:00 | 2015-04-01 00:00:00 |
|  4 |             222222 |      RPC       | CND             | 2011-10-01 00:00:00 | 2015-04-01 00:00:00 |
|  5 |             333333 |      RPC       | IA              | 2011-10-01 00:00:00 | 2015-04-01 00:00:00 |
|  6 |             333333 |      RPC       | CND             | 2011-10-01 00:00:00 | NULL                |
+----+--------------------+----------------+-----------------+---------------------+---------------------+

如果end_dateNULL，则关联计划被视为活动。 我试图提出一个查询，该查询将返回仅关联inactive计划的帐户列表或计数。基于上述内容，它将222222，因为它有两个与之关联的计划，但都标记为非活动。

+----+--------------------+----------------+-----------------+---------------------+---------------------+
| id |     account_id     | attribute_key  | attribute_value | start_date          | end_date            |
+----+--------------------+----------------+-----------------+---------------------+---------------------+
|  3 |             222222 |      RPC       | IA              | 2011-10-01 00:00:00 | 2015-04-01 00:00:00 |
|  4 |             222222 |      RPC       | CND             | 2011-10-01 00:00:00 | 2015-04-01 00:00:00 |
+----+--------------------+----------------+-----------------+---------------------+---------------------+

Answer 1

在派生表(子查询(中，您可以获取只有"非活动计划"的account_id值的列表：

SELECT account_id 
FROM plan 
GROUP BY account_id 
HAVING COUNT(end_date IS NULL) = 0 /* No plan with null end date */

现在，您可以使用此子查询连接回plan表，以获取该account_id的所有计划：

SELECT p.* 
FROM plan AS p 
JOIN (
SELECT account_id 
FROM plan 
GROUP BY account_id 
HAVING COUNT(end_date IS NULL) = 0
) AS dt ON dt.account_id = p.account_id 

Answer 2

我认为没问题。

SELECT
p.*
FROM
Plan
INNER JOIN Account a USING (account_id)
WHERE p.account_id NOT IN (
SELECT p2.account_id FROM Plan p2 WHERE p2.end_date IS NULL
);