php - 将数据分成星期几 mysql

Question

你好，

我目前正在制作一个销售跟踪报告，用户可以在其中选择一个日期范围，然后表格应该会显示结果。

现在我有这个查询来计算售出的商品数量

select x_transaction_details.xitem, 
       SUM(x_transaction_details.qty) as totalNumberSold, 
       count(x_transaction_details.xitem) as occurence, 
       x_transaction_details.cost, 
       i_inventory.xitem, 
       x_transaction_details.date_at as transDate
from x_transaction_details
   left join i_inventory on x_transaction_details.xitem = i_inventory.xid
where (x_transaction_details.date_at BETWEEN '2015-08-13 08:34:12' AND '2015-09-14 08:34:12')
GROUP BY x_transaction_details.xitem
ORDER BY occurence DESC

这个查询显示

|itemName| totalNumberSold | occurence | date
|item 1  | 23 pcs          |  2        |
|item 2  | 18 pcs          |  6        |
|item 3  | 203 pcs         |  18       |
etc..

现在我想知道每天的销售额明细，所以我尝试了

select x_transaction_details.xitem, 
       SUM(x_transaction_details.qty) as sold, 
       count(x_transaction_details.xitem) as occurence,
       x_transaction_details.cost, 
       i_inventory.xitem, 
       x_transaction_details.date_at as transDate
       SUM(CASE WHEN date_at = DAYOFWEEK(1) THEN    
                 count(x_transaction_details.xitem) END) as Sunday
from x_transaction_details
  left join i_inventory on x_transaction_details.xitem = i_inventory.xid
where (x_transaction_details.date_at BETWEEN '2015-08-13 08:34:12' AND '2015-09-14 08:34:12')
GROUP BY x_transaction_details.xitem
ORDER BY occurence DESC

但它却产生了一个错误。我想创建一个更详细的表格

|itemName|Mon|Tue|Wed|Thur|Fri|Sat|Sun| totalNumberSold | occurence | date
|item 1  | 10|  0| 0 | 13 | 0 |0  |0  | 23 pcs          |  2        |
|item 2  | 1 |  3| 12|  0 | 16|0  |0  | 32 pcs          |  6        |
|item 3  | 0 |  6| 1 | 13 | 8 |7  |1  | 36 pcs          |  12       |
etc..

感谢您的提示，我也可以使用 php(硬方式)制作此表，但我猜它也可以使用 sql 查询。祝你有美好的一天。

Answer 1

您缺少逗号和嵌套聚合函数。我想你想要:

select i.xid, SUM(td.qty) as sold, count(td.xitem) as occurrence,
       avg(td.cost) as avg_cost, i.xitem,
       SUM(case when DAYOFWEEK(td.date_at) = 1 then td.qty else 0 end) as Sunday
from i_inventory i join
     x_transaction_details td       
     on td.xitem = i.xid
where td.date_at BETWEEN '2015-08-13 08:34:12' AND '2015-09-14 08:34:12'
GROUP BY i.xid
ORDER BY occurrence DESC;

注意事项:

• “发生”有两个 r。
• 表别名使查询更易于编写和阅读。
• 不能嵌套聚合函数。
• 您不应在 select 列表中包含 date_at，因为每一行的值不是唯一的。
• left join 要么是向后的(库存应该在前)要么应该是 inner join。在一个格式正确的数据库中，交易表中不应有不在库存表中的项目(我不认为)。