本文介绍了Mysql每天选择每个类别的数量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有三张桌子.
categories
表格
id |姓名
items
表格
id |姓名 |category_id
订单
表
id |item_id |日期时间 |交易代码
现在如何为查询中的每一天的每个类别选择具有特定交易代码的总订单.
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每天选择每个类别的数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!