>我有一个名为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
;
但它给我抛出了错误:
错误 1146 (42S02(:表"统一推送.filtered"不存在
如何筛选/限制原始表,以便可以对该新表应用其他查询?
您的查询通常不好。无需进行所有这些子查询,您可以使用一个简单的查询来实现。
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)
切换,使您可以进行条件计数。
查询中的问题是过滤别名的范围:它在其他子查询中不可见。
如果你真的想要拥有它,你可以创建一个视图:
create view filtered as SELECT * FROM push_message_info WHERE DATE(submit_date) > '2017-05-14';
SELECT p.pending, s.success, t.total
FROM
(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;
好吧,我可以告诉你我是如何完成这样的事情的。尝试:
SELECT SUM(IF(served < total, 1, 0)) as pending,
SUM(IF(served = total,1 ,0)) AS success,
COUNT(*) as total
FROM push_message_info
WHERE DATE(submit_date) > '2017-05-14';
这应该给你你需要的东西...