如何获得一个月中每一天的价值 [英] How to get values for every day in a month
本文介绍了如何获得一个月中每一天的价值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
数据:
values date
14 1.1.2010
20 1.1.2010
10 2.1.2010
7 4.1.2010
...
有关2010年1月的示例查询应获得31行.每天一次.并应添加值.现在,我可以使用31个查询来执行此操作,但是我希望可以使用一个查询.有可能吗?
sample query about january 2010 should get 31 rows. One for every day. And values vould be added. Right now I could do this with 31 queries but I would like this to work with one. Is it possible?
结果:
1. 34
2. 10
3. 0
4. 7
...
推荐答案
这实际上在SQL中很难做到.一种方法是使用带有UNION ALL的长选择语句来生成从1到31的数字.这说明了原理,但为清楚起见,我停在4:
This is actually surprisingly difficult to do in SQL. One way to do it is to have a long select statement with UNION ALLs to generate the numbers from 1 to 31. This demonstrates the principle but I stopped at 4 for clarity:
SELECT MonthDate.Date, COALESCE(SUM(`values`), 0) AS Total
FROM (
SELECT 1 AS Date UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
--
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 31) AS MonthDate
LEFT JOIN Table1 AS T1
ON MonthDate.Date = DAY(T1.Date)
AND MONTH(T1.Date) = 1 AND YEAR(T1.Date) = 2010
WHERE MonthDate.Date <= DAY(LAST_DAY('2010-01-01'))
GROUP BY MonthDate.Date
使用表存储这些值并与其连接可能会更好.
It might be better to use a table to store these values and join with it instead.
结果:
1, 34
2, 10
3, 0
4, 7
这篇关于如何获得一个月中每一天的价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文