时间戳

首页 > 解决方案 > 将四个 MySQL 查询减少到一个

mysql - 将四个 MySQL 查询减少到一个

问题描述

我正在运行四个语句来查看有多少预约已过期、今天到期、本周到期或任何没有预约的记录（未使用）。

我有两张表，一张用于记录（联系人），另一张用于单对多关系中的约会（约会）。每个联系人在 [contacts] 表中都是唯一的，但在 [appointments] 表中可以有多个记录（以维护历史记录），并且最高的约会.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

[编辑] 感谢神风敢死队，我已经对此进行了调整，使前三个查询工作，只是努力获得最后一个（返回零，而不是实际计数）：

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

标签： mysql

解决方案

我恰好回答了一个非常相似的问题：

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

推荐阅读