Table A have two date fields, respectively is a. do v.begin and A.e nd, when I want to statistics on the number of records in 2009, the SQL statement is written so:

 
Select count (*) 
From A A 
Where to_char (a. do v.begin, 'yyyy) & lt;='2009' and to_char (a.e nd, 'yyyy) & gt; '2009' 

Now coming from the front of the user to select two year values, the nf1 and nf2, nf1 is less than nf2, the nf1 is 2009, for example, nf2 is 2017, and then to the statistics from 2009 to 2017, every year the number of records displayed in the following form:

Number of years
2009, 221,
2010, 322,
2011, 732,
.
2017, 102,

The idea now is according to the incoming nf1 and nf2 dynamic splicing union all to do this, but I don't want to use a union all, because it's so influence efficiency,
Union all thoughts of the SQL statement is as follows (2009-2011) :

 
Select as the year of 2009, the count (*) 
From A A 
Where to_char (a. do v.begin, 'yyyy) & lt;='2009' and to_char (a.e nd, 'yyyy) & gt; '2009' 
Union all 
Select as the year of 2010, the count (*) 
From A A 
Where to_char (a. do v.begin, 'yyyy) & lt;='2010' and to_char (a.e nd, 'yyyy) & gt; '2010' 
Union all 
Select as the year of 2011, the count (*) 
From A A 
Where to_char (a. do v.begin, 'yyyy) & lt;='2011' and to_char (a.e nd, 'yyyy) & gt; '2011' 

CodePudding user response:

For New Year's data, if measured by start time

 select to_char (a. do v.begin, 'yyyy), count (*) 
From A A 
Where to_char (a. do v.begin, 'yyyy) & lt; 
='nf1'And to_char (a.e nd, 'yyyy) & gt; 'nf2' 
Group by to_char (a. do v.begin, 'yyyy) 

CodePudding user response:

Statistical nf1 to nf2 where all data should be [begin<=nf2 and end> nf1],
Just thought for a moment, the description of the problem can be simplified, the statistical data from 2016 and 2017, for [to_char (a. do v.begin, 'yyyy) & lt;=' nf 'and to_char (a.e nd,' yyyy) & gt; 'nf] such conditions, the records in the table may be belongs to 2016 to 2017, can I use [begin

reference 1st floor nayi_224 response:

for New Year's data, if measured by start time

 select to_char (a. do v.begin, 'yyyy), count (*) 
From A A 
Where to_char (a. do v.begin, 'yyyy) & lt; 
='nf1'And to_char (a.e nd, 'yyyy) & gt; 'nf2' 
Group by to_char (a. do v.begin, 'yyyy) 

CodePudding user response:

Nf1 to nf2 all data of the where condition so write as if also not

refer to the second floor Pisererer response:

statistics nf1 to nf2 where all data should be [begin<=nf2 and end> nf1],
Just thought for a moment, the description of the problem can be simplified, the statistical data from 2016 and 2017, for [to_char (a. do v.begin, 'yyyy) & lt;=' nf 'and to_char (a.e nd,' yyyy) & gt; 'nf] such conditions, the records in the table may be belongs to 2016 to 2017, can I use [begin

Quote: refer to 1st floor nayi_224 response:

For New Year's data, if measured by start time

 select to_char (a. do v.begin, 'yyyy), count (*) 
From A A 
Where to_char (a. do v.begin, 'yyyy) & lt; 
='nf1'And to_char (a.e nd, 'yyyy) & gt; 'nf2' 
Group by to_char (a. do v.begin, 'yyyy) 

CodePudding user response:

? The query SELECT

http://www.verejava.com/? Id=17173779389953

CodePudding user response:

reference Pisererer reply: 3/f

nf1 to nf2 all data of the where condition so write as if also not

Quote: refer to the second floor Pisererer response:

Statistical nf1 to nf2 where all data should be [begin<=nf2 and end> nf1],
Just thought for a moment, the description of the problem can be simplified, the statistical data from 2016 and 2017, for [to_char (a. do v.begin, 'yyyy) & lt;=' nf 'and to_char (a.e nd,' yyyy) & gt; 'nf] such conditions, the records in the table may be belongs to 2016 to 2017, can I use [begin

Quote: refer to 1st floor nayi_224 response:

For New Year's data, if measured by start time

 select to_char (a. do v.begin, 'yyyy), count (*) 
From A A 
Where to_char (a. do v.begin, 'yyyy) & lt; 
='nf1'And to_char (a.e nd, 'yyyy) & gt; 'nf2' 
Group by to_char (a. do v.begin, 'yyyy) 

Want to across years of data statistics of once for each year?

 with tab1 as (
Select 1 id, the date '2017-01-01' be, the date '2018-01-01' en the from dual union all 
Select 2 id, the date '2015-01-01' be, the date '2018-01-01' en the from dual union all 
Select 3 id, the date '2018-01-01' be, the date '2018-08-01' en the from dual 
) 

Select count (1), to_char (add_months (be, 12 * (level 1)), 'yyyy) 
The from tab1 t1 
Connect by trunc (add_months (be, 12 * (level 1)), 'y') & lt;=trunc (en, 'y') 
And the prior id=id 
Value is not null and the prior dbms_random. 
Group by to_char (add_months (be, 12 * (level 1)), 'yyyy);