mysql - 获取不同时间段的数据

Question

我有这个查询

SELECT concat(order_delivery_data.order_delivery_data_name,' sent'),
       sum(case `order`.order_status when 'sent' then 1 else 0 end) '0-7 days'
FROM order_delivery_data
     INNER JOIN `order` ON order_delivery_data.order_id = `order`.order_id
where order_delivery_data.order_delivery_data_name in ('Казахстан КАЗПОЧТА')
      and order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
UNION
SELECT concat(order_delivery_data.order_delivery_data_name,' parcelonaplace'),
      sum(case `order`.order_status when 'parcel-on-a-place' then 1 else 0 end) parcelonaplace
FROM order_delivery_data
     INNER JOIN `order` ON order_delivery_data.order_id = `order`.order_id
where order_delivery_data.order_delivery_data_name in ('Казахстан КАЗПОЧТА')
      and order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) 

显示 2 列。如何在像这样的同一查询中多获取一列？

Answer 1

在 case 语句中使用条件聚合并更改 where 子句。根据所需的结果，您可能需要更改第二个总和以使用日期范围，而不仅仅是 > 14 天的间隔。

SELECT concat(order_delivery_data.order_delivery_data_name,' sent'),

sum(case When `order`.order_status = 'sent' 
     AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) then 1 else 0 end) '0-7 days',
sum(case When `order`.order_status = 'sent' 
     AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) then 1 else 0 end) '8-14 days'    
FROM order_delivery_data
INNER JOIN `order` ON order_delivery_data.order_id = `order`.order_id
WHERE order_delivery_data.order_delivery_data_name in ('Казахстан КАЗПОЧТА')
  AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY)

UNION

SELECT concat(order_delivery_data.order_delivery_data_name,' parcelonaplace'),
sum(case when `order`.order_status = 'parcel-on-a-place' 
     AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) then 1 else 0 end) `parcelonaplace 0-7`,
sum(case when `order`.order_status = 'parcel-on-a-place' 
     AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) then 1 else 0 end) `parcelonaplace 8-14

FROM order_delivery_data
INNER JOIN `order` ON order_delivery_data.order_id = `order`.order_id
WHERE order_delivery_data.order_delivery_data_name in ('Казахстан КАЗПОЧТА')
  AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY)