mysql - 将 4 个 MySQL 查询减少为 1 个

Question

这个问题已经有答案了:

multiple query same table but in different columns mysql (4 个回答)

已关闭 3 年前。

我正在运行四个报表来查看有多少预约已到期、今天到期、本周到期或任何没有预约(未使用)的记录​​。

我有两个表，一个用于记录(联系人)，另一个用于单对多关系中的约会(约会)。每个联系人在 [联系人] 表中都是唯一的，但在 [约会] 表中可以有多个记录(以维护历史记录)，并且最高的约会。id 是联系人的最近约会。

所以，目前我有:

SELECT COUNT(*) AS "Late" FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1 AND a.appointment < CURDATE()

SELECT COUNT(*) AS "Today" FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1 AND DATE_FORMAT(a.appointment, "%Y-%m-%d") = DATE_FORMAT(NOW(), "%Y-%m-%d")

SELECT COUNT(*) AS "This Week" FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1 AND a.appointment > CURDATE() + INTERVAL 1 DAY AND a.appointment < CURDATE() + INTERVAL 7 DAY

SELECT COUNT(*) AS "Unused" FROM contacts WHERE active = 1 AND id NOT IN (SELECT contact_id FROM appointments)

这些工作正常，但我想我可以尝试将它们压缩到一个查询中以运行得更快(？)，这就是我遇到困难的地方，我尝试了许多变体:

SELECT 
    CASE 
        WHEN a.appointment < CURDATE() THEN "Late" 
        WHEN DATE_FORMAT(a.appointment, "%Y-%m-%d") = DATE_FORMAT(NOW(), "%Y-%m-%d") THEN "Today" 
        WHEN a.appointment > CURDATE() + INTERVAL 1 DAY AND a.appointment < CURDATE() + INTERVAL 7 DAY THEN "This Week" 
        WHEN c.id NOT IN (SELECT contact_id FROM appointments) THEN "Unused" 
        ELSE "Error"
    END AS "Status", 
    COUNT(a.appointment) AS "Count"
FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1

<小时/>

[编辑]感谢Kamikaze我已经对此进行了调整，让前三个查询可以正常工作，只是努力获取最后一个查询(返回零，而不是实际计数):

SELECT 
    SUM(CASE WHEN a.appointment < CURDATE() THEN 1 ELSE 0 END) AS "Late",
    SUM(CASE WHEN DATE_FORMAT(a.appointment, "%Y-%m-%d") = DATE_FORMAT(NOW(), "%Y-%m-%d") THEN 1 ELSE 0 END) AS "Today",
    SUM(CASE WHEN a.appointment > CURDATE() + INTERVAL 1 DAY AND a.appointment < CURDATE() + INTERVAL 7 DAY THEN 1 ELSE 0 END) AS "This Week",
    SUM(CASE WHEN c.id NOT IN (SELECT contact_id FROM appointments) THEN 1 ELSE 0 END) AS "Unused"
FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1

Answer 1

我只是碰巧回答了very similar question :

答案是使用 SELECT SUM(CASE WHEN ... END) 作为 col1，SUM(CASE WHEN ... END) 作为 col2，等等，其中每个 CASE 返回 1 或 0。