select count(case when y.yearsold>10 and y.yearsold<20 then 1 else 0 end),
count(case when y.yearsold>20 and y.yearsold<30 then 1 else 0 end),
count(case when y.yearsold>30 and y.yearsold<40 then 1 else 0 end)
from (select sysdate-to_date(t.birth,'yyyymmdd) yearsold from tablename t) y
count(case when y.yearsold>20 and y.yearsold<30 then 1 else 0 end),
count(case when y.yearsold>30 and y.yearsold<40 then 1 else 0 end)
from (select sysdate-to_date(t.birth,'yyyymmdd) yearsold from tablename t) y
解决方案 »
- 求助~~一个关于oracle关闭远程登录的问题
- 请问有什么办法能将ACCESS数据导入ORACLE?
- 不同版本之间的dblink,可以吗?
- 求救 Oracle Intelligent Agent Executable 错误
- oracle怎样像sql Server那样将表结构及其数据库相关的scipt倒出到文本文件?
- oracle数据库备份的问题
- 请问关于客户端连接服务器oracle的问题
- 两张表的SQL查询:超难!
- oracle中这句怎么写 if EXISTS( Select c from t where c='x')
- 如何在window2000下用命令行启动数据库
- 如何将创建好的数据库导出到另一台机器上?
- 一个关于存储过程的简单问题,高手指点
count(case when y.yearsold>20 and y.yearsold<30 then 1 else 0 end),
count(case when y.yearsold>30 and y.yearsold<40 then 1 else 0 end)
from (select sysdate-to_date(t.birth,'yyyymmdd') yearsold from tablename t) y刚才少个引号
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;
的方法比较好,还是用这个吧!我的,效率差点
2 SUM(DECODE(trunc((to_char(sysdate,'YYYYmmdd')-birth))/10),2,1,0)),
3 SUM(DECODE(trunc((TO_CHAR(sysdate,'YYYYmmdd')-birth))/10),3,1,0))
4 FROM haha;
SUM(DECODE(trunc((to_char(sysdate,'YYYYmmdd')-birth))/10),2,1,0)),
*
ERROR 位于第 2 行:
ORA-00909: 无效的自变量数
这是怎么回事呢??
TO_CHAR(BIRTH,'YYYY')
create table haha(
id varchar2(8),
name varchar2(8),
birth varchar2(8)
);
insert into haha(id,name,birth) values('001','AAA','19791021');
...
谢谢解答!!!
SUM(DECODE(TRUNC((TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(BIRTH,'YYYY'))/10),2,1,0)),
被我换为
sum(decode(trunc((to_char(sysdate,'YYYYmmdd')-birth))/10),2,1,0)),
这样的形式,其他的两个sum也是一样
请指教 2,1.0 这几个参数是什么含义啊?
被我换为
sum(decode(trunc((to_char(sysdate,'YYYYmmdd')-substr(birth,1,4)))/10),2,1,0)),decode(n,2,1,0) --表示当n=2时 表达式返回1,否则返回0
sum(decode(floor(trunc(sysdate,'yyyy')-trunc(birth,'yyyy')/10),2,1,0)) "20-30岁",
sum(decode(floor(trunc(sysdate,'yyyy')-trunc(birth,'yyyy')/10),3,1,0)) "30-40岁"