是关于EMP表的:(Oracle实现的)
创建一个查询显示所有雇员的数量以及分别在1980、1981、1982、1983年加入公司的雇员数量。答案参考:
CREATE TABLE EMP( start_year VARCHAR2(8))
DELETE FROM EMP
INSERT INTO EMP VALUES('19800101');
INSERT INTO EMP VALUES('19800101');
INSERT INTO EMP VALUES('19810101');
INSERT INTO EMP VALUES('19810101');
INSERT INTO EMP VALUES('19820101');
INSERT INTO EMP VALUES('19820101');
INSERT INTO EMP VALUES('19830101');
INSERT INTO EMP VALUES('19830101');
INSERT INTO EMP VALUES('19830101');
INSERT INTO EMP VALUES('19830101');
INSERT INTO EMP VALUES('19810101');
INSERT INTO EMP VALUES('19820101');
INSERT INTO EMP 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 EMP
where substr(start_year,1,4) in ('1981','1980','1982','1983')result:
sum1 emp_1980 emp_1981 emp_1982 emp_1983
13 3 3 3 4如果记录蛮多,不只四年,还有1984,1985 .......等等?
如果还用
sum(decode(substr(start_year,1,4),'1984',1,0)) emp_1984 ???????
那这个SQL语句就没有意义了,请问一高手,有什么好的改进方法啊?
创建一个查询显示所有雇员的数量以及分别在1980、1981、1982、1983年加入公司的雇员数量。答案参考:
CREATE TABLE EMP( start_year VARCHAR2(8))
DELETE FROM EMP
INSERT INTO EMP VALUES('19800101');
INSERT INTO EMP VALUES('19800101');
INSERT INTO EMP VALUES('19810101');
INSERT INTO EMP VALUES('19810101');
INSERT INTO EMP VALUES('19820101');
INSERT INTO EMP VALUES('19820101');
INSERT INTO EMP VALUES('19830101');
INSERT INTO EMP VALUES('19830101');
INSERT INTO EMP VALUES('19830101');
INSERT INTO EMP VALUES('19830101');
INSERT INTO EMP VALUES('19810101');
INSERT INTO EMP VALUES('19820101');
INSERT INTO EMP 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 EMP
where substr(start_year,1,4) in ('1981','1980','1982','1983')result:
sum1 emp_1980 emp_1981 emp_1982 emp_1983
13 3 3 3 4如果记录蛮多,不只四年,还有1984,1985 .......等等?
如果还用
sum(decode(substr(start_year,1,4),'1984',1,0)) emp_1984 ???????
那这个SQL语句就没有意义了,请问一高手,有什么好的改进方法啊?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货