select sum(case when price>=101 and price<=200 then 1 else 0 end) as '101元~200元', sum(case when price>=201 and price<=300 then 1 else 0 end) as '201元~300元', sum(case when price>=301 and price<=400 then 1 else 0 end) as '301元~400元' from tablename group by price;
樓上的做法好象不對吧,按Price分組怎麼行呢?如果按Price分組,則隻有相同Price值的行才會進入SUM中進行CASE中的判斷,所以這是錯誤的! 所以根據樓主的意思,去掉你那個分組條件就OK了: select sum(case when price>=101 and price<=200 then 1 else 0 end) as '101元~200元', sum(case when price>=201 and price<=300 then 1 else 0 end) as '201元~300元', sum(case when price>=301 and price<=400 then 1 else 0 end) as '301元~400元' from tablename;
确实不应该加group,还有最好加上nvl的判断 select nvl(sum(case when price>=101 and price<=200 then 1 else 0 end),0) as '101元~200元', nvl(sum(case when price>=201 and price<=300 then 1 else 0 end),0) as '201元~300元', nvl(sum(case when price>=301 and price<=400 then 1 else 0 end),0) as '301元~400元' from tablename;
sum(case when price>=201 and price<=300 then 1 else 0 end) as '201元~300元',
sum(case when price>=301 and price<=400 then 1 else 0 end) as '301元~400元'
from tablename
group by price;
不过还有问题price 可以直接是列名吗??还是要事先声明:DECLARE
price tablename.年龄%TYPE;
……
所以根據樓主的意思,去掉你那個分組條件就OK了:
select sum(case when price>=101 and price<=200 then 1 else 0 end) as '101元~200元',
sum(case when price>=201 and price<=300 then 1 else 0 end) as '201元~300元',
sum(case when price>=301 and price<=400 then 1 else 0 end) as '301元~400元'
from tablename;
select nvl(sum(case when price>=101 and price<=200 then 1 else 0 end),0) as '101元~200元',
nvl(sum(case when price>=201 and price<=300 then 1 else 0 end),0) as '201元~300元',
nvl(sum(case when price>=301 and price<=400 then 1 else 0 end),0) as '301元~400元'
from tablename;