按周分组，并选择本周的记录 [英] Group by week and select records from current week

问题描述

我一直在尝试使用MySQL显示每周销售结果的表格，其中一周的结果应该如下：

I have been trying to display a table of results for weekly sales using MySQL, where the results of the week should be like:

Sun - Mon - Tues - Wed - Thur - Fri - Sat

所以我继续前进使用此查询：

So I went ahead and used this query:

SELECT DATE_FORMAT(cart_calendar.datefield, '%d-%b') AS DATE
        , IFNULL(SUM(cart_daily_sales.quantity),0) AS total_sales 
FROM cart_daily_sales 
RIGHT JOIN cart_calendar 
         ON (DATE(cart_daily_sales.order_date) = cart_calendar.datefield)
WHERE (cart_calendar.datefield) 
GROUP BY WEEK(cart_calendar.datefield)

我知道我几乎在那里，这一年输出了52周的所有时间，我只想要当前周的结果，不像上面的代码，导致这个...

I know I'm almost there as this outputs all of the 52 weeks in the year, I only want the current week result unlike the above code that results to this...

'02-Jan', '09-Jan', '16-Jan', '23-Jan', '30-Jan', '06-Feb', '13-Feb', '20-Feb', '27-Feb', '06-Mar' e.t.c....

我知道我可以在今天的日期使用BETWEEN运算符，然后-7天，但这并不显示本周，如果今天是星期三，星期三将显示最后七天到周三，而不是正确的星期结构。

I know I can use the BETWEEN operator for today's date and then -7 days but this doesn't show the current week, if today's day is Wednesday it will show the last seven days to last weeks Wednesday, not the correct week structure.

如何显示上周每周的销售量？

How do I display last week's sales grouped per week?

推荐答案

 SELECT DATE_FORMAT(cc.datefield, '%d-%b') AS DATE
        , SUM(IFNULL(cds.quantity,0)) AS total_sales 
 FROM cart_calendar cc
 INNER JOIN cart_daily_sales cds
         ON (DATE(cds.order_date) = cc.datefield)
 WHERE WEEK(cc.datefield) = WEEK(now())
 GROUP BY WEEK(cc.datefield)

或者如果你想要每天：

 SELECT DATE_FORMAT(cc.datefield, '%d-%b') AS DATE
        , SUM(IFNULL(cds.quantity,0)) AS total_sales 
 FROM cart_calendar cc
 INNER JOIN cart_daily_sales cds
         ON (DATE(cds.order_date) = cc.datefield)
 WHERE WEEK(cc.datefield) = WEEK(now())
 GROUP BY MOD(WEEKDAY(cc.datefield)+1,6) WITH ROLLUP

这篇关于按周分组，并选择本周的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！