oracle 查询公司的人数,以及在1980-1987年之间,每年雇用的人数,结果类似下面的格式
答案:4 悬赏:50
解决时间 2021-01-10 06:02
- 提问者网友:遁入空寂
- 2021-01-09 22:33
oracle 查询公司的人数,以及在1980-1987年之间,每年雇用的人数,结果类似下面的格式
最佳答案
- 二级知识专家网友:琴狂剑也妄
- 2021-01-09 22:47
其实你想说的,就是一个行列转换的问题,目前来说,行列转换有几种普遍的方法,像DECODE函数,case when等等,至于用哪种,看个人习惯了,下面简单说一下:
decode转换行列比较常用,像你的问题,可以这样:
SELECt COUNT(*) AS "TOTAL",
SUM(DECODE(YEAR,'1987',1,0)) AS "1987",
SUM(DECODE(YEAR,'1988',1,0)) AS "1988",
SUM(DECODE(YEAR,'1989',1,0)) AS "1989"
FROM ORCL;
--DECODE函数,意思是,YEAR字段如果是1987,那么返回1,否则返回0,然后用sum函数进行统计,这样就能统计每一年有多少人。当然,case when也能起到同样的作用。
decode转换行列比较常用,像你的问题,可以这样:
SELECt COUNT(*) AS "TOTAL",
SUM(DECODE(YEAR,'1987',1,0)) AS "1987",
SUM(DECODE(YEAR,'1988',1,0)) AS "1988",
SUM(DECODE(YEAR,'1989',1,0)) AS "1989"
FROM ORCL;
--DECODE函数,意思是,YEAR字段如果是1987,那么返回1,否则返回0,然后用sum函数进行统计,这样就能统计每一年有多少人。当然,case when也能起到同样的作用。
全部回答
- 1楼网友:慢性怪人
- 2021-01-10 00:55
select count(*) ,count(case when year= 1980 then 1 else 0) 1980,
count(case when year= 1981 then 1 else 0) 1981,
count(case when year= 1982 then 1 else 0) 1982,
count(case when year= 1987 then 1 else 0) 1987
from table where year in ('1980','1981','1982','1987')
count(case when year= 1981 then 1 else 0) 1981,
count(case when year= 1982 then 1 else 0) 1982,
count(case when year= 1987 then 1 else 0) 1987
from table where year in ('1980','1981','1982','1987')
- 2楼网友:举杯邀酒敬孤独
- 2021-01-10 00:22
下面的要用sum,不是count
select count(*) ,
sum(case when year= 1980 then 1 else 0) 1980,
sum(case when year= 1981 then 1 else 0) 1981,
sum(case when year= 1982 then 1 else 0) 1982,
sum(case when year= 1987 then 1 else 0) 1987
from table where year in ('1980','1981','1982','1987')
select count(*) ,
sum(case when year= 1980 then 1 else 0) 1980,
sum(case when year= 1981 then 1 else 0) 1981,
sum(case when year= 1982 then 1 else 0) 1982,
sum(case when year= 1987 then 1 else 0) 1987
from table where year in ('1980','1981','1982','1987')
- 3楼网友:孤独入客枕
- 2021-01-09 23:45
SELECt COUNT(*) TOTAL,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1980', 1, 0)) YEAR1980,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1981', 1, 0)) YEAR1981,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1982', 1, 0)) YEAR1982,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1983', 1, 0)) YEAR1983,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1984', 1, 0)) YEAR1984,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1985', 1, 0)) YEAR1985,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1986', 1, 0)) YEAR1986,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1987', 1, 0)) YEAR1987
FROM EMP
用SCOOT验证过的,不过只有oracle支持DECODE。
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1980', 1, 0)) YEAR1980,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1981', 1, 0)) YEAR1981,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1982', 1, 0)) YEAR1982,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1983', 1, 0)) YEAR1983,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1984', 1, 0)) YEAR1984,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1985', 1, 0)) YEAR1985,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1986', 1, 0)) YEAR1986,
SUM(DECODE((TO_CHAr(HIREDATE, 'YYYY')), '1987', 1, 0)) YEAR1987
FROM EMP
用SCOOT验证过的,不过只有oracle支持DECODE。
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯