我有一个表Account,我正在查询它以根据销售记录识别旧帐户.一个帐户在属性表中可以有多行.物业可能会被关闭.如果帐户上的所有属性都已关闭,则应按旧帐户退还该帐户.
我如何确定所有物业都已关闭的帐户?
此外,如果客户在24个月内未购买服务,则视为旧帐户.
例如,给出下表:
-- ACCOUNT --
+-------+-----------------------------------+
| ac_id | ac_email |
+-------+-----------------------------------+
| 1416 | bob@bob.com |
| 1419 | joe@joe.com |
+-------+-----------------------------------+
-- 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 | bob@bob.com | FALSE | 9 | 2011-02-16 |
| 1419 | joe@joe.com | TRUE | 6,7 | 2022-11-14 |
+------------+-----------------------+----------------+---------------------------+--------------+------------------+
Bob的账户退还是因为他已经两年没有服务了,而Joe的账户退还是因为他已经服务了两年,但他的所有房产都被关闭了.
这是我当前的查询
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列,但我不确定如何获取.我需要一个子查询吗?