根据JOIN结果计算列中的值

Question

我有一个表Account，我正在查询它以根据销售记录识别旧帐户。一个帐户在Property表中可能有多行。一处房产可能会被关闭。如果一个帐户上的所有属性都已关闭，则应将该帐户作为旧帐户返回。

如何识别关闭所有房产的账户？

此外，如果账户在24个月内未购买服务，则视为旧账户。

举个例子，给定以下表格：

-- ACCOUNT --
+-------+-----------------------------------+
| ac_id | ac_email                          |
+-------+-----------------------------------+
|  1416 | [e-mail]                       |
|  1419 | [e-mail]                       |
+-------+-----------------------------------+
-- PROPERTY --
+------+---------------+-------+
| p_id | p_closed      | ac_id |
+------+---------------+-------+
|    3 |  FALSE        |  1416 |
|    6 |  TRUE         |  1419 |
|    7 |  TRUE         |  1419 |
+------+---------------+-------+
--SERVICE--
+------+------------+
| p_id | s_saledate |
+------+------------+
|    3 | 2010-03-17 |
|    3 | 2011-02-16 |
|    6 | 2022-11-14 |
|    7 | 2022-01-24 |
+------+------------+

我希望返回以下表格

+------------+-----------------------+-----------------------+--------------+------------+--------------+------------------+
| account_id | email                 | all_properties_closed | property_ids | latest_sale_date |
+------------+-----------------------+-----------------------+--------------+------------+--------------+------------------+
|       1416 | [e-mail]           |       FALSE           |     9            | 2011-02-16       |
|       1419 | [e-mail]           |       TRUE            |    6,7           | 2022-11-14       |
+------------+-----------------------+----------------+---------------------------+--------------+------------------+

Bob的账户被退回，因为他已经两年多没有服务了&乔的账户被退回，因为他已经服务了两年，但他的所有财产都被关闭了。

这是我当前的查询

SELECT
ac_id as account_id,
GROUP_CONCAT(DISTINCT p_id) as property_ids,
MAX(sale_date) as latest_sale_date
FROM Account
JOIN Property USING (ac_id)
JOIN Service USING (p_id)
GROUP BY ac.ac_id
HAVING latest_sale_date < 2021-03-31

我想我需要使用ALL运算符和WHERE来获得all_properties_closed列，但我不确定如何获得。我需要子查询吗？

Answer 1

您可以使用条件聚合计算all_properties_closed。假设p_closed是一个类似字符串的数据类型，它看起来像：

SELECT
ac_id as account_id,
MIN(p_closed = 'FALSE') all_properties_closed
GROUP_CONCAT(DISTINCT p_id) as property_ids,
MAX(sale_date) as latest_sale_date
FROM Account
JOIN Property USING (ac_id)
JOIN Service USING (p_id)
GROUP BY ac.ac_id
HAVING latest_sale_date < 2021-03-31 OR all_properties_closed

如果组中的任何p_closed具有非'FALSE'值，则表达式MIN(p_closed = 'FALSE')产生0，否则返回1；我们可以使用这些信息在CCD_ 10子句中进行筛选。

在正常使用中，p_closed列中会有一个0/1标志，我们可以这样做：

MIN(p_closed) all_properties_closed

Answer 2

是的，您可以使用子查询来检查帐户的所有属性是否已关闭。以下是您的查询的修改版本，添加了all_properties_closed列：

SELECT
ac.ac_id as account_id,
ac.ac_email as email,
GROUP_CONCAT(DISTINCT p.p_id) as property_ids,
MAX(s.s_saledate) as latest_sale_date,
NOT EXISTS (
SELECT 1 FROM Property p2 WHERE p2.ac_id = ac.ac_id AND p2.p_closed = FALSE
) as all_properties_closed
FROM Account ac
LEFT JOIN Property p ON p.ac_id = ac.ac_id
LEFT JOIN Service s ON s.p_id = p.p_id
GROUP BY ac.ac_id
HAVING latest_sale_date < 2021-03-31 OR all_properties_closed

在子查询中，我们检查同一帐户是否存在未关闭的属性(p2.p_closed = FALSE)。如果没有这样的属性，则NOT EXISTS返回TRUE，表示所有属性都已关闭。

请注意，我还将ac.ac_email添加到了选择列表中，并使用LEFT JOIN而不是JOIN来确保我们获得所有帐户，即使它们没有任何属性或服务。

末尾的HAVING子句包括识别旧帐户的两个标准：在指定日期后没有服务的帐户，或关闭所有财产的帐户。