select NVL (sum (synnum), 0) value, to_char (syntime, 'yyyy - MM - dd) lable from synlog where syntype in (4, 5, 6) and trunc (syntime) & gt; Trunc (sysdate) - 30 group by to_char (syntime, 'MM - dd yyyy -'); 

At present this data can only get a
Hope the date can be extended from the day of article 30 automatic completion date
The query data for
The value lable
465 the 2017-12-19

I hope it's
The value lable
0, 2017-12-20
465 the 2017-12-19
0, 2017-12-18
.
0, 2017-11-20
A total of 30

CodePudding user response:

Table data and field posted

CodePudding user response:

Generate a date in the middle of the table (can be written as a temporary table), the main table, then left to connect your table
Date to the date of the table in the middle of the left association, the date of your table

Select the date and the sum from table a and table b where a. d. ate=b.d ate group by the table in the middle of the date

CodePudding user response:

 
With t1 as (
The select level as id, to_date (' 2017-12-19 ', '- dd yyyy - mm) + 2 - level as date1 from dual 
Connect by level<6) 
, t2 (value, lable) as (
SELECT 1 AS "value", trunc (sysdate) AS lable from dual 
Union all 
The SELECT 9 AS "value", trunc (sysdate - 4) AS lable from dual) 
The select t1 date1, NVL (t2) value, 0) AS value1 from t1 left join t2 on t1. Date1=t2. Lable ORDER BY date1 
/* 
DATE1 VALUE1 
1 2017/12/16 9 
2 2017/12/17 0 
3 2017/12/18 0 
4 2017/12/19 0 
5 2017/12/20 1 
*/
Change the inside of the T2 to your query can