求一sql语句

Question

最笨的办法select  (select count(*) from table where year<1950) column1,.......
from table

Answer 1

select * from testb bid
1930
1935
1968
...
create or replace function test_t(p_in number) return number as
    p_out number(8);
begin 
    if p_in=1950 then 
        select count(*) into p_out from testb b where b.id <p_in;
    elsif p_in >2000 then 
        select count(*) into p_out from testb b where b.id>p_in;
    elsif p_in=2000 then 
        select count(*) into p_out from testb b where b.id>=p_in-10 and b.id<=p_in;
    else
        select count(*) into p_out from testb b where b.id>=p_in-10 and b.id<p_in;
    end if;
    return p_out;
end;select 
    sum(decode(id,1950,test_t(id),0)) "&ETH;&iexcl;&Oacute;&Uacute;1950",
    sum(decode(id,1960,test_t(id),0)) "1960",
    sum(decode(id,1970,test_t(id),0)) "1970",
    sum(decode(id,1980,test_t(id),0)) "1980",
    sum(decode(id,1990,test_t(id),0)) "1990",
    sum(decode(id,2000,test_t(id),0)) "2000",
    sum(decode(id,2001,test_t(id),0)) "&acute;ó&Oacute;&Uacute;2000" 
from (select distinct id
     from (select decode(sign((ceil(b.id/10)*10)-1950),-1,1950,decode(sign((ceil(b.id/10)*10)-2000),1,2001,0,2000,(ceil(b.id/10)*10))) id from testb b order by 1))

Answer 2

不用这么麻烦，你只要用一个子查询就可以了：select count(1), a.name from (
    select case when year < 1950 then '<1950'
                when year < 1960 then '1960'
                when year < 1970 then '1970'
                when year < 1980 then '1980'
                when year < 1990 then '1990'
                when year < 2000 then '2000'
                else '>2000'
    from table) a
group by a.name;现在是按照你的year 也是 number 类型处理的。
你在比较的时候，如果year 和 1950 等类型不同，结果出不来，你可以使用函数将表达式两边的数据类型统一了就可以。

Answer 3

sorry
忘了加end:
select count(1), a.name from (
    select case when year < 1950 then '<1950'
                when year < 1960 then '1960'
                when year < 1970 then '1970'
                when year < 1980 then '1980'
                when year < 1990 then '1990'
                when year < 2000 then '2000'
                else '>2000'
           end
    from table) a
group by a.name;

Answer 4

不知这招如何，没有测试。select count(a.year),a.year from (
    select decode(sign(year-1950),-1,'<1950',
           decode(sign(year-1960),-1,'1960',
           decode(sign(year-1970),-1,'1970',
           decode(sign(year-1980),-1,'1980',
           decode(sign(year-1990),-1,'1990',
           decode(sign(year-2000),-1,'2000','>2000'))))))) a;

Answer 5

不对，我搞错了。应该是：select count(a.year),a.year from (
    select decode(sign(year-1950),-1,'<1950',
           decode(sign(year-1960),-1,'1960',
           decode(sign(year-1970),-1,'1970',
           decode(sign(year-1980),-1,'1980',
           decode(sign(year-1990),-1,'1990',
           decode(sign(year-2000),-1,'2000','>2000')))))) from table) a group by a.year;没测试。

Answer 6

再次改正：select count(a.year),a.year from (
    select decode(sign(year-1950),-1,'<1950',
           decode(sign(year-1960),-1,'1960',
           decode(sign(year-1970),-1,'1970',
           decode(sign(year-1980),-1,'1980',
           decode(sign(year-1990),-1,'1990',
           decode(sign(year-2000),-1,'2000','>2000')))))) year from table) a group by a.year;仍没测试。:)

Answer 7

看看这个，对你可能有用。
http://community.csdn.net/Expert/topic/3355/3355732.xml?temp=.2675135

Answer 8

select count(a.year),a.year from (
    select decode(sign(year-1950),-1,'<1950',
           decode(sign(year-1960),-1,'1960',
           decode(sign(year-1970),-1,'1970',
           decode(sign(year-1980),-1,'1980',
           decode(sign(year-1990),-1,'1990',
           decode(sign(year-2000),-1,'2000','>2000')))))) year from table) a group by a.year;