统计的很奇怪的问题200分相送!

Question

SELECT SUM(DECODE(TRUNC((SYSDATE-BIRTH)/10),1,1,0)) '10-19',
SUM(DECODE(TRUNC((SYSDATE-BIRTH)/10),2,1,0)) '20-29',
SUM(DECODE(TRUNC((SYSDATE-BIRTH)/10),3,1,0)) '30-39'
FROM TBNAME;

Answer 1

select sum(ten)  "10-20岁",sum(twen)  "20-30岁" ,sum(thirt)  "30-40岁" from
(
select count(*) ten,0 twen,0 thirt from table1
where birth between add_months(sysdate,-240) and add_months(sysdate,-120)
union
select 0 ten,count(*) twen,0 thirt from table1
where birth between add_months(sysdate,-360) and add_months(sysdate,-240)
union
select 0 ten,0 twen,count(*) thirt from table1
where birth between add_months(sysdate,-480) and add_months(sysdate,-360)
)

Answer 2

hdkkk(diablo2) 谢谢！！！
问题基本解决！！
bzszp(SongZip）？？是否能把您的方法给解释一下

Answer 3

SELECT SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(BIRTH,'YYYY'))/10),1,1,0)),
SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(BIRTH,'YYYY'))/10),2,1,0)),
SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(BIRTH,'YYYY'))/10),3,1,0))
 FROM TBNAME;
SQL> SELECT * FROM TEMP;TN
----------
01-1月 -90
01-1月 -80
01-1月 -80
01-1月 -70
01-1月 -03SQL> SELECT SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(TN,'YYYY'))/10),1,1,0)),
  2  SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(TN,'YYYY'))/10),2,1,0)),
  3  SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(TN,'YYYY'))/10),3,1,0))
  4   FROM TEMP;SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(TN,'YYYY'))/10),1,1,0))
-------------------------------------------------------------------------
SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(TN,'YYYY'))/10),2,1,0))
-------------------------------------------------------------------------
SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(TN,'YYYY'))/10),3,1,0))
-------------------------------------------------------------------------
                                                                        1
                                                                        2
                                                                        1
SQL> 

Answer 4

DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(BIRTH,'YYYY'))/10),1,1,0)TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(BIRTH,'YYYY') --年龄
TRUNC(../10) --除以10后 取整

Answer 5

http://expert.csdn.net/Expert/topic/2429/2429913.xml?temp=3.463382E-02