行转列问题 select count(*), count(case to_char(hiredate,'yyyy') when '1980' then 1 end) "1980", count(case to_char(hiredate 'yyyy') when '1981' then 1 end )"1981", count(case to_char(hiredate,'yyyy') when '1982' then 1 end) "1982" from emp
total怎么会是14,应该是12 吧,两种方法如下: --decode select sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, '1981', 1, '1982', 1)) "total", sum(decode(to_char(hiredate, 'yyyy'), '1980', 1)) "1980", sum(decode(to_char(hiredate, 'yyyy'), '1981', 1)) "1981", sum(decode(to_char(hiredate, 'yyyy'), '1982', 1)) "1982" from scott.emp; --case when select sum(case to_char(hiredate, 'yyyy') when '1980' then 1 when '1981' then 1 when '1982' then 1 end) "total", sum(case to_char(hiredate, 'yyyy') when '1980' then 1 end) "1980", sum(case to_char(hiredate, 'yyyy') when '1981' then 1 end) "1981", sum(case to_char(hiredate, 'yyyy') when '1982' then 1 end) "1982" from scott.emp;
原来如此,在count里用case啊 !我之前就一直在想,要怎么做才能把行变成列呢,我还一直在纠结这里要怎么用group by 原来根本不用还想请教下,还有没有其他行转换列的方法呢?
total怎么会是14,应该是12 吧,两种方法如下: --decode select sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, '1981', 1, '1982', 1)) "total", sum(decode(to_char(hiredate, 'yyyy'), '1980', 1)) "1980", sum(decode(to_char(hiredate, 'yyyy'), '1981', 1)) "1981", sum(decode(to_char(hiredate, 'yyyy'), '1982', 1)) "1982" from scott.emp; --case when select sum(case to_char(hiredate, 'yyyy') when '1980' then 1 when '1981' then 1 when '1982' then 1 end) "total", sum(case to_char(hiredate, 'yyyy') when '1980' then 1 end) "1980", sum(case to_char(hiredate, 'yyyy') when '1981' then 1 end) "1981", sum(case to_char(hiredate, 'yyyy') when '1982' then 1 end) "1982" from scott.emp;总数14是因为还有其他年份聘用的人!这个方法也不错,虽然比1楼的稍微复杂了点(思路上倒是一样的),我之前也想过把符合年份的行变为1 然后用sum累加,但具体实现就不会了这个有参考价值喔! 话说我要怎么给分呢?
total怎么会是14,应该是12 吧,两种方法如下: --decode select sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, '1981', 1, '1982', 1)) "total", sum(decode(to_char(hiredate, 'yyyy'), '1980', 1)) "1980", sum(decode(to_char(hiredate, 'yyyy'), '1981', 1)) "1981", sum(decode(to_char(hiredate, 'yyyy'), '1982', 1)) "1982" from scott.emp; --case when select sum(case to_char(hiredate, 'yyyy') when '1980' then 1 when '1981' then 1 when '1982' then 1 end) "total", sum(case to_char(hiredate, 'yyyy') when '1980' then 1 end) "1980", sum(case to_char(hiredate, 'yyyy') when '1981' then 1 end) "1981", sum(case to_char(hiredate, 'yyyy') when '1982' then 1 end) "1982" from scott.emp;总数14是因为还有其他年份聘用的人!这个方法也不错,虽然比1楼的稍微复杂了点(思路上倒是一样的),我之前也想过把符合年份的行变为1 然后用sum累加,但具体实现就不会了这个有参考价值喔! 话说我要怎么给分呢? 哦,我还以为你要对三个年份求一个总和呢,如果是全部总和就简单了,直接count(*)就行,分随便给
select count(*),
count(case to_char(hiredate,'yyyy') when '1980' then 1 end) "1980",
count(case to_char(hiredate 'yyyy') when '1981' then 1 end )"1981",
count(case to_char(hiredate,'yyyy') when '1982' then 1 end) "1982"
from emp
--decode
select sum(decode(to_char(hiredate, 'yyyy'),
'1980',
1,
'1981',
1,
'1982',
1)) "total",
sum(decode(to_char(hiredate, 'yyyy'), '1980', 1)) "1980",
sum(decode(to_char(hiredate, 'yyyy'), '1981', 1)) "1981",
sum(decode(to_char(hiredate, 'yyyy'), '1982', 1)) "1982"
from scott.emp;
--case when
select sum(case to_char(hiredate, 'yyyy')
when '1980' then
1
when '1981' then
1
when '1982' then
1
end) "total",
sum(case to_char(hiredate, 'yyyy')
when '1980' then
1
end) "1980",
sum(case to_char(hiredate, 'yyyy')
when '1981' then
1
end) "1981",
sum(case to_char(hiredate, 'yyyy')
when '1982' then
1
end) "1982"
from scott.emp;
--decode
select sum(decode(to_char(hiredate, 'yyyy'),
'1980',
1,
'1981',
1,
'1982',
1)) "total",
sum(decode(to_char(hiredate, 'yyyy'), '1980', 1)) "1980",
sum(decode(to_char(hiredate, 'yyyy'), '1981', 1)) "1981",
sum(decode(to_char(hiredate, 'yyyy'), '1982', 1)) "1982"
from scott.emp;
--case when
select sum(case to_char(hiredate, 'yyyy')
when '1980' then
1
when '1981' then
1
when '1982' then
1
end) "total",
sum(case to_char(hiredate, 'yyyy')
when '1980' then
1
end) "1980",
sum(case to_char(hiredate, 'yyyy')
when '1981' then
1
end) "1981",
sum(case to_char(hiredate, 'yyyy')
when '1982' then
1
end) "1982"
from scott.emp;总数14是因为还有其他年份聘用的人!这个方法也不错,虽然比1楼的稍微复杂了点(思路上倒是一样的),我之前也想过把符合年份的行变为1 然后用sum累加,但具体实现就不会了这个有参考价值喔!
话说我要怎么给分呢?
--decode
select sum(decode(to_char(hiredate, 'yyyy'),
'1980',
1,
'1981',
1,
'1982',
1)) "total",
sum(decode(to_char(hiredate, 'yyyy'), '1980', 1)) "1980",
sum(decode(to_char(hiredate, 'yyyy'), '1981', 1)) "1981",
sum(decode(to_char(hiredate, 'yyyy'), '1982', 1)) "1982"
from scott.emp;
--case when
select sum(case to_char(hiredate, 'yyyy')
when '1980' then
1
when '1981' then
1
when '1982' then
1
end) "total",
sum(case to_char(hiredate, 'yyyy')
when '1980' then
1
end) "1980",
sum(case to_char(hiredate, 'yyyy')
when '1981' then
1
end) "1981",
sum(case to_char(hiredate, 'yyyy')
when '1982' then
1
end) "1982"
from scott.emp;总数14是因为还有其他年份聘用的人!这个方法也不错,虽然比1楼的稍微复杂了点(思路上倒是一样的),我之前也想过把符合年份的行变为1 然后用sum累加,但具体实现就不会了这个有参考价值喔!
话说我要怎么给分呢?
哦,我还以为你要对三个年份求一个总和呢,如果是全部总和就简单了,直接count(*)就行,分随便给