A table, but need to be done according to the selection in (recent,6,9,12 3 months), according to the statistics and compared with the same want to ask god to how to write
SELECT rq "month", ((value - old_sum)/old_sum * 100 | | '%') "year-on-year," value "total" from
(
Select to_char (y.c RJSJ, 'YYYYMM') as rq, count (1) as the value, (select count (1) as the value from yw_t_jy y1 where to_char (y1 CRJSJ, 'YYYYMM') & gt;=to_char (add_months (TO_DATE (start, 'YYYYMM'), and 12), 'YYYYMM') and to_char (y1 CRJSJ, 'YYYYMM') & lt;=to_char (add_months (TO_DATE (end, 'yyyymm'), and 12), 'yyyymm') and y1. FXQD=0
Group by to_char (y1. CRJSJ, 'YYYYMM')) old_sum from yw_t_jy y where to_char (y.c RJSJ, 'YYYYMM') & gt;=start and to_char (y.c RJSJ, 'YYYYMM') & lt;=end and y.f XQD=0
Group by to_char (y.c RJSJ, 'YYYYMM') order by rq
)
This is what I write, if only for a month can also calculate, but can't get more than month, always prompt single subquery return multiple rows

CodePudding user response:

The building Lord, study the lag and lead these two functions, to suit your current needs;

CodePudding user response:

Ok, thanks, I went to take a look

CodePudding user response:

The problem on the subquery:

 select count (1) as the value from yw_t_jy y1 where to_char (y1 CRJSJ, 'YYYYMM') & gt;=to_char (add_months (TO_DATE (start, 'YYYYMM'), and 12), 'YYYYMM') and to_char (y1 CRJSJ, 'YYYYMM') & lt;=to_char (add_months (TO_DATE (end, 'yyyymm'), and 12), 'yyyymm') and y1. FXQD=0 
Group by to_char (y1. CRJSJ, 'YYYYMM') 

here will return multiple result sets,
Rose should be able to use add_months (12) to achieve
Should be able to use lag compared to the (1) take the previous line