I'd like to pick up on April 16 to April 20 this time within the scope of the data, by date,


The current SQL is: select sum (sl) AS 'sl' riqi from biao where riqi>='2020-04-16' AND riqi<='2020-04-20' group by riqi

Returns the result is:

Sl riqi

10 20200416
16 20200417
19 20200419

But I want that as a result, the number is 0, what also have no words how to write the SQL?

Sl riqi

10 20200416
16 20200417
0, 20200418
19 20200419
0 20200420

CodePudding user response:

O nested

Select * from (select * from date table where the date between datefmd and ateto) a
Left the join
(select sum (sl) AS 'sl' riqi from biao where riqi between '2020-04-16' AND '2020-04-20 s' group by riqi) b on a. date=b.r iqi

CodePudding user response:

The inside of the time-series statistics "sampling under fill 0
"If this is opentsdl temporal database write down sampling rules directly by

If it is a traditional relational database
Advice into CTE or stored procedure

Have STH over and over again in a statement, it belongs to the scope of exotic curiosity-a solution looking, can be discussed, but is not recommended,
For example,
SELECT
X=ROW_NUMBER () OVER
(the ORDER BY s1. [object_id])
The FROM sys. Objects AS s1

Such a statement we can create a sequence 1, 2, 3, 4, with the time function we can automatically generate the date of the 1st floor table,

But again, it belongs to the scope of exotic curiosity-a solution looking, can be discussed, but is not recommended

CodePudding user response:

The master.. Spt_values using this stuff actually can also, but and sys. Objects, length limited
Infinite actually also, how tall two cartesian product line

But we can discuss when a problem, but I don't recommend that you take these when remember baby

CodePudding user response:

This can completely in the back-end code in figure out whether the day data is empty, if written in SQL see some requirements can be whole fixed writing, such as building a table to store a year to date, then the associated

CodePudding user response:

Dynamic line column,
Data center display Null, you can directly through the isnull (field, 0) shows
https://blog.csdn.net/hanjun0612/article/details/56673854