如何获取一对多连接中的空记录计数

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_date为NULL，则认为关联的计划处于活动状态。我试图拿出一个查询，将返回列表或帐户计数，其中只有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
);