是关于EMP表的:
创建一个查询显示所有雇员的数量以及分别在1980、1981、1982、1983年加入公司的雇员数量。
我要这些数据成为一行数据应该怎么写?
创建一个查询显示所有雇员的数量以及分别在1980、1981、1982、1983年加入公司的雇员数量。
我要这些数据成为一行数据应该怎么写?
解决方案 »
- new_students := total_students (:major.class_id, 'N')中的:major是什么意思?
- Oracle包内过程重载的问题
- 如何用SQL 语句实现如下. 横纵转制的结果(超难)
- redhat linux as3 下安装oracle9i问题,急!!在线等候!
- 存储过程出错
- 请问oracle是否有通用,备注这两个数据类型?
- 请问在SQL PLUS 里查看本机上有几个数据库啊!
- 数据库设计问题
- 有谁知道Rapid Sql7的注册码? 谢谢(100分)
- P4上安装Oracle8i问题,急请高手帮忙啊!
- 请教 , Can not allocate log
- 高手看看,这个删除的算法怎么做效率最高?
我要出来的是一行数据:
sum 1980 1981 1982 1983
----------------------------
n n n n n
sum(decode(start_year,'1980',counts)) emp_1980,
sum(decode(start_year,'1981',counts)) emp_1981,
sum(decode(start_year,'1982',counts)) emp_1982,
sum(decode(start_year,'1983',counts)) emp_1983
from( select start_year,count(*) counts from emp where start_year in('1980','1981','1982','1983')
group by start_year);
sum(decode(to_char(start_year,'yyyy'),'1980',1,0)) emp_1980,
sum(decode(to_char(start_year,'yyyy'),'1981',1,0)) emp_1981,
sum(decode(to_char(start_year,'yyyy'),'1982',1,0)) emp_1982,
sum(decode(to_char(start_year,'yyyy'),'1983',1,0)) emp_1983
from tablename
where to_char(start_year,'yyyy') in ('1981','1980','1982','1983')
你的条件检索出来应该都是NULL吧,除了总计.
根据你的代码,第二到第五个字段,每个有3个NULL与COUNT的相加,这样会导致你得到的结果是NULL.
DELETE FROM test
INSERT INTO test VALUES('19800101');
INSERT INTO test VALUES('19800101');
INSERT INTO test VALUES('19810101');
INSERT INTO test VALUES('19810101');
INSERT INTO test VALUES('19820101');
INSERT INTO test VALUES('19820101');
INSERT INTO test VALUES('19830101');
INSERT INTO test VALUES('19830101');
INSERT INTO test VALUES('19830101');
INSERT INTO test VALUES('19830101');
INSERT INTO test VALUES('19810101');
INSERT INTO test VALUES('19820101');
INSERT INTO test VALUES('19800101');
select count(1) sum1,
sum(decode(substr(start_year,1,4),'1980',1,0)) emp_1980,
sum(decode(substr(start_year,1,4),'1981',1,0)) emp_1981,
sum(decode(substr(start_year,1,4),'1982',1,0)) emp_1982,
sum(decode(substr(start_year,1,4),'1983',1,0)) emp_1983
from test
where substr(start_year,1,4) in ('1981','1980','1982','1983')
result:
sum1 emp_1980 emp_1981 emp_1982 emp_1983
1 13 3 3 3 4
DROP TABLE test
sum(decode(to_char(start_year,'yyyy'),'1980',1,0)) emp_1980,
sum(decode(to_char(start_year,'yyyy'),'1981',1,0)) emp_1981,
sum(decode(to_char(start_year,'yyyy'),'1982',1,0)) emp_1982,
sum(decode(to_char(start_year,'yyyy'),'1983',1,0)) emp_1983
from tablename
count(decode(to_char(start_year,'yyyy'),'1980',1)) emp_1980,
count(decode(to_char(start_year,'yyyy'),'1981',1)) emp_1981,
count(decode(to_char(start_year,'yyyy'),'1982',1)) emp_1982,
count(decode(to_char(start_year,'yyyy'),'1983',1)) emp_1983
from tablename
上面sbaz(万神渡劫)也指出了我的错误,不知这样行不行?
create table ht_test(start_year varchar2(16));
insert into ht_test values('1980');
insert into ht_test values('1981');
insert into ht_test values('1981');
insert into ht_test values('1983');
insert into ht_test values('1983');
insert into ht_test values('1983');
insert into ht_test values('1983');
insert into ht_test values('1984');
insert into ht_test values('1985');
insert into ht_test values('1986');
insert into ht_test values('1984');SQL> select sum(counts) sum,
2 nvl(sum(decode(start_year,'1980',counts,0)),0) emp_1980,
3 nvl(sum(decode(start_year,'1981',counts,0)),0) emp_1981,
4 nvl(sum(decode(start_year,'1982',counts,0)),0) emp_1982,
5 nvl(sum(decode(start_year,'1983',counts,0)),0) emp_1983
6 from( select start_year,count(*) counts from ht_test group by start_year); SUM EMP_1980 EMP_1981 EMP_1982 EMP_1983
---------- ---------- ---------- ---------- ----------
11 1 2 0 4