Mysql每天选择每个类别的数量 [英] Mysql select count per category per day

问题描述

我有三张桌子.

categories 表格

id |姓名

items 表格

id |姓名 |category_id

id | name | category_id

订单表

id |item_id |日期时间 |交易代码

id | item_id | date_time | transaction_code

现在如何为查询中的每一天的每个类别选择具有特定交易代码的总订单.

Now how can I select total order's with specific transaction code for each category for every day in my query.

预期结果:

Date        Category Total
2013-12-01  001      6
2013-12-01  002      0
2013-12-01  003      1
2013-12-02  001      14
2013-12-02  002      2
2013-12-02  003      0
2013-12-03  001      11
2013-12-03  002      4
2013-12-03  003      4

编写此查询的最有效方法是什么.

What would be the most efficient way to write this query.

这是我目前尝试过的:

SELECT DATE(od.trans_date) AS Date, ct.number AS Category, COUNT(od.id) AS Total 
from orders od
JOIN items im ON od.item_id = im.id
JOIN categories ct ON im.category_id = ct.id
WHERE od.trans_date BETWEEN '2013-12-01 00:00:00' AND '2013-12-03 23:59:59'
AND (od.transaction_code = 17 OR od.transaction_code = 15)
GROUP BY DATE(od.trans_date), ct.number ASC;

谢谢

推荐答案

我建议将您的 JOINS 更改为 LEFT JOINS 以便您的结果将包括不有任何物品...

I would suggest changing your JOINS to LEFT JOINS so that your results will include orders that do not have any items...

SELECT DATE(od.trans_date) AS Date, ct.number AS Category, COUNT(od.id) AS Total 
from orders od
LEFT JOIN items im ON od.item_id = im.id
LEFT JOIN categories ct ON im.category_id = ct.id
WHERE od.trans_date BETWEEN '2013-12-01 00:00:00' AND '2013-12-03 23:59:59'
AND (od.transaction_code = 17 OR tr.transaction_code = 15)
GROUP BY DATE(od.trans_date), ct.number ASC;

此外，您可能需要切换 JOINS 的顺序，以便显示所有类别，而不是所有订单.

Also, you might need to switch the order of your JOINS so that all categories show up, and not all orders.

SELECT DATE(od.trans_date) AS Date, ct.number AS Category, COUNT(od.id) AS Total 
FROM categories ct
LEFT JOIN items im ON ct.id = im.category_id
LEFT JOIN orders od ON im.id = od.item_id
WHERE od.trans_date BETWEEN '2013-12-01 00:00:00' AND '2013-12-03 23:59:59'
AND (od.transaction_code = 17 OR tr.transaction_code = 15)
GROUP BY DATE(od.trans_date), ct.number ASC;

这篇关于Mysql每天选择每个类别的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！