php - 在 MySQL 中通过单个查询获取今天、本周、本月和今年的数据？

Question

我正在从数据库中检索今天的数据，我想检索本月，今年的数据，是否可以在单个查询中进行？或者我应该对每个项目进行单独的查询？我的查询如下，

    select sum(it.rate * it.quantity)as sales from invoices i
join invoice_items it on i.id = it.invoice_id
where i.invoice_date > DATE_SUB(NOW(), INTERVAL 1 DAY)

Answer 1

使用条件聚合:

select sum(case when i.invoice_date > DATE_SUB(NOW(), INTERVAL 1 DAY) then it.rate * it.quantity
            end) as sales_1day,
       sum(case when i.invoice_date > DATE_SUB(NOW(), INTERVAL 7 DAY) then it.rate * it.quantity
           end) as sales_7day,
       sum(case when i.invoice_date > DATE_SUB(NOW(), INTERVAL 1 MONTH) then it.rate * it.quantity
           end) as sales_1month,
       sum(case when i.invoice_date > DATE_SUB(NOW(), INTERVAL 1 YEAR) then it.rate * it.quantity
           end) as sales_1year       
from invoices i join
     invoice_items it
     on i.id = it.invoice_id