急、急、急

Question

現在有這樣一個sql語句： 
select to_char(min(diary_date),'yyyy') year from hp_health_diary 
假如得到的結果是2007，求一sql語句可以得到2007到當前年份的年份list，也就是得到 
2009 
2008 
2007 
這樣的效果

Answer 1

SELECT TO_CHAR(TO_DATE('2001-12-01','YYYY-MM-DD'),'YYYY')+ROWNUM FROM DUAL
CONNECT BY TO_CHAR(TO_DATE('2001-12-01','YYYY-MM-DD'),'YYYY')+ROWNUM<=TO_CHAR(SYSDATE,'YYYY')

Answer 2

select to_char(min(diary_date),'yyyy')+ROWNUM year from hp_health_diary
CONNECT BY  to_char(min(diary_date),'yyyy')+ROWNUM <=TO_CHAR(SYSDATE,'YYYY')

Answer 3

connect by 下面不能用群體函數啊~

Answer 4

OPER@tl> select * from test;AAA
-------------------
2009-03-19 10:57:17
2007-02-01 10:57:20
2008-03-17 10:57:22OPER@tl> select to_char(aaa,'yyyy')-1+rownum
  2  from test
  3  where aaa in(select min(aaa) from test)
  4  connect by to_number(to_char(aaa,'yyyy'))+rownum<=to_char(sysdate,'yyyy')
  5  /TO_CHAR(AAA,'YYYY')-1+ROWNUM
----------------------------
                        2007
                        2008
                        2009OPER@tl> 

Answer 5

select to_char(YEAR,'yyyy')+ROWNUM year1 from 
(SELECT MIN(diary_date) YEAR FROM hp_health_diary )
CONNECT BY  to_char(YEAR,'yyyy')+ROWNUM <=TO_CHAR(SYSDATE,'YYYY')

Answer 6

修改下：
select to_char(YEAR,'yyyy')-1+ROWNUM year1 from 
(SELECT MIN(diary_date) YEAR FROM hp_health_diary )
CONNECT BY  to_char(YEAR,'yyyy')-1+ROWNUM <=TO_CHAR(SYSDATE,'YYYY')