返回不存在数据的空行 [英] return empty rows for not existsting data
问题描述
好,我有一个带有 date
列和 integer
列的表,我想检索所有行在特定日期范围内按日期进行分组;由于每天没有行,是否有可能使mysql返回带有默认值的那几天的行?
Ok, i have a table with a date
column and a integer
column, and i want to retrieve all the rows
grouped by date's day within a certain date range; since there are not rows for every day, is it possible to make mysql return rows for those days with a default value?
源表:
date value
2020-01-01 1
2020-01-01 2
2020-01-03 2
2020-01-07 3
2020-01-08 4
2020-01-08 1
按日期和 sum
ming值对 group
进行分组后的标准行为:
Standard behaviour after group
ing by date and sum
ming values:
2020-01-01 3
2020-01-03 2
2020-01-07 3
2020-01-08 5
具有空行的所需行为/结果:
Desired behaviour/result with empty rows:
2020-01-01 3
2020-01-02 0
2020-01-03 2
2020-01-04 0
2020-01-05 0
2020-01-06 0
2020-01-07 3
2020-01-08 5
推荐答案
您可以执行以下操作:
# table creation:
drop table if exists test_table;
create table test_table (your_date date, your_value int(11));
insert into test_table (your_date, your_value) values ('2020-01-01', 1);
insert into test_table (your_date, your_value) values ('2020-01-01', 2);
insert into test_table (your_date, your_value) values ('2020-01-03', 2);
insert into test_table (your_date, your_value) values ('2020-01-07', 3);
insert into test_table (your_date, your_value) values ('2020-01-08', 4);
insert into test_table (your_date, your_value) values ('2020-01-08', 1);
这将创建一个基本上所有日期的列表.然后,您可以过滤感兴趣的日期,并与表格和组一起加入
This creates a list of basically all the dates. You then filter for the dates your interested in, join with your table and group.
您还可以将where语句中的日期替换为子查询(表的最小和最大日期)以使其动态化
You could also replace the dates in the where statement with subqueries (min and max date of your table) to make it dynamic
这是一个变通办法,但是可以.
It's a bit of a work-around but it works.
select sbqry.base_date, sum(ifnull(t.your_value, 0))
from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) base_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) sbqry
left join test_table t on base_date = t.your_date
where sbqry.base_date between '2020-01-01' and '2020-01-08'
group by sbqry.base_date;
输入:
+------------+------------+
| your_date | your_value |
+------------+------------+
| 2020-01-01 | 1 |
| 2020-01-01 | 2 |
| 2020-01-03 | 2 |
| 2020-01-07 | 3 |
| 2020-01-08 | 4 |
| 2020-01-08 | 1 |
+------------+------------+
输出:
+------------+------------------------------+
| base_date | sum(ifnull(t.your_value, 0)) |
+------------+------------------------------+
| 2020-01-01 | 3 |
| 2020-01-02 | 0 |
| 2020-01-03 | 2 |
| 2020-01-04 | 0 |
| 2020-01-05 | 0 |
| 2020-01-06 | 0 |
| 2020-01-07 | 3 |
| 2020-01-08 | 5 |
+------------+------------------------------+
这篇关于返回不存在数据的空行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!