mysql - 从一个查询中的日期获取统计信息(或以快速方式)

Question

我有一个表:apns_devices 包含列:estado、modified、pid 等我想根据最后修改时间获取一些统计信息，即上周、上个月、昨天、今天和总修改时间。现在我正在为每个时期做一个查询(嵌套)，例如:

SELECT t1.estado, t1.siempre, t2.hoy
FROM 
(SELECT *, COUNT(pid) as siempre FROM `apns_devices` GROUP BY estado) as t1
INNER JOIN 
(SELECT *, COUNT(pid) as hoy   FROM `apns_devices` WHERE DATE (modified) = CURRENT_DATE GROUP BY estado) as t2 
ON t1.estado = t2.estado

这是今天和总计

为了加速脚本； ¿如何在一个查询中执行此操作？

Answer 1

SELECT
  estado,
  COUNT(*) siempre,
  COUNT(CASE WHEN DATE(modified) = CURDATE() THEN 1 END) AS ModifiedToday,
  COUNT(CASE WHEN DATEDIFF(CURDATE(), modified) = 1 THEN 1 END) AS ModifiedYesterday,
  COUNT(CASE WHEN DATEDIFF(CURDATE(), modified) BETWEEN 2 AND 7 THEN 1 END) AS ModifiedLastWeek,
  COUNT(CASE WHEN DATEDIFF(CURDATE(), modified) BETWEEN 8 AND 30 THEN 1 END) AS ModifiedYesterMonth
FROM
  apns_devices
GROUP BY
  estado

请参阅 fiddle here .