mysql - 在 MySQL 中应用查询之前过滤表

Question

我有一个名为 push_message_info 的 mysql 表，我想过滤它然后计算一些结果。获取结果的查询是:

SELECT p.pending, s.success, t.total
FROM 
    (SELECT count(*) AS pending FROM push_message_info WHERE served < total) AS p,
    (SELECT count(*) AS success FROM push_message_info WHERE served = total) AS s,
    (SELECT count(*) AS total FROM push_message_info) AS t
;

问题是我只想计算最新的条目。因为它有一个名为 submit_date 的日期时间字段，所以我认为这样的事情会起作用:

SELECT p.pending, s.success, t.total
FROM 
    (SELECT * FROM push_message_info WHERE DATE(submit_date) > '2017-05-14') AS filtered
    (SELECT count(*) AS pending FROM filtered WHERE served < total) AS p,
    (SELECT count(*) AS success FROM filtered WHERE served = total) AS s,
    (SELECT count(*) AS total FROM filtered) AS t
;

但它抛出错误:

ERROR 1146 (42S02): Table 'unifiedpush.filtered' doesn't exist

如何过滤/限制原始表，以便我可以对该新表应用其他查询？

Answer 1

您的查询一般来说不是很好。您可以通过一个简单的查询来完成所有这些子查询。

SELECT 
    SUM(CASE WHEN served < total THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN served = total THEN 1 ELSE 0 END) AS served,
    COUNT(*) as total
FROM push_message_info
WHERE DATE(submit_date) > '2017-05-14';

我已经切换了count s(total 除外)与 SUM(CASE WHEN ... THEN 1 ELSE 0 END)这让您可以进行条件计数。