MySQL 一周中每天的总计

Question

我的购物车收入似乎因一周中的每一天而异，周一和周四是低收入日，周三和周六是高收入日。因此，我想显示一个显示总计的网页，如下所示:

Week #    Sun    Mon    Tue    Wed    Thu    Fri    Sat
Week 1   $5.00  $1.00  $3.00  $9.00  $1.00  $3.00  $9.00
Week 2   $5.23  $1.07  $2.98  $8.75  $0.02  $3.14  $7.51
Week 3   etc.

我可以像这样查询一周中的某一天:

SELECT count( id ) AS orders, 
       order_date, 
       date_format( order_date, '%a' ) AS weekday, 
       sum( total) AS revenue
FROM `ss_orders`
WHERE dayofweek( order_date ) = 1
      AND order_date >= date_add( now( ) , INTERVAL -83 DAY )
GROUP BY order_date
ORDER BY order_date DESC

这为我提供了过去 12 周内所有星期日的每日总计。因此，我可以执行 7 个查询来获得我需要的内容(一周中的每一天进行 1 个查询)。看来我应该能够在一次查询中获得全部信息。

查询应该是什么？谢谢!

编辑:这是来自首选解决方案的更正查询。

SELECT 
  week( o.order_date ) as WkNumber,
  sum( if( weekday( o.order_date ) = 6, 1, 0 ) * o.total ) as SalesSun,
  sum( if( weekday( o.order_date ) = 6, 1, 0 )) as OrdersSun,
  sum( if( weekday( o.order_date ) = 0, 1, 0 ) * o.total ) as SalesMon,
  sum( if( weekday( o.order_date ) = 0, 1, 0 )) as OrdersMon,
  sum( if( weekday( o.order_date ) = 1, 1, 0 ) * o.total ) as SalesTue,
  sum( if( weekday( o.order_date ) = 1, 1, 0 )) as OrdersTue,
  sum( if( weekday( o.order_date ) = 2, 1, 0 ) * o.total ) as SalesWed,
  sum( if( weekday( o.order_date ) = 2, 1, 0 )) as OrdersWed,
  sum( if( weekday( o.order_date ) = 3, 1, 0 ) * o.total ) as SalesThu,
  sum( if( weekday( o.order_date ) = 3, 1, 0 )) as OrdersThu,
  sum( if( weekday( o.order_date ) = 4, 1, 0 ) * o.total ) as SalesFri,
  sum( if( weekday( o.order_date ) = 4, 1, 0 )) as OrdersFri,
  sum( if( weekday( o.order_date ) = 5, 1, 0 ) * o.total ) as SalesSat,
  sum( if( weekday( o.order_date ) = 5, 1, 0 )) as OrdersSat,
  sum( o.total ) as SalesWeek,
  sum( 1 ) as OrdersWeek
from
  ss_orders o
where
  o.order_date > date_add( now(), INTERVAL -13 WEEK )
group by
  week( o.order_date )
order by o.order_date desc

Answer 1

按周分组然后按天分组？

SELECT count( id ) AS orders, 
       date_format( order_date, '%a' ) AS weekday, 
       WEEK(order_date) AS week_number,
       sum( total) AS revenue
FROM 
       `ss_orders`
WHERE 
       order_date >= date_add( now( ) , INTERVAL -83 DAY )
GROUP BY 
       WEEK(orderdate), DAY(orderdata)
ORDER BY 
       order_date DESC

应该给你这样的结果:

orders | weekday | week_number | revenue
5        Sun       1             20
6        Mon       1             15
...