你想要什么样的sql语句?
在sql server里也不可能一个select就能满足你说的条件吧
在sql server里也不可能一个select就能满足你说的条件吧
解决方案 »
- 急,在线等-->>存过怎么不能运行?
- sqlplus能登陆,但是使用pl/sql developer却不能
- 数据库连接出错
- update 简单问题提示单行字返回多行
- 请求关于动态游标的使用问题(附代码)
- 【偶结贴一向很快 ^_^】问个简单的存储过程代码----错在哪里?
- 求救:从SQL 2000到oracle9i的数据迁移!!
- 为什么我访问数据库时,我见到数据库里面的中文都是乱码?
- Oracle的口令是什么啊?
- 在Oracle视图中加入order by得出了错误的结果是什么情况
- 是不是有oracle 10 了? 有中文的吗? windows 2003 能装oracle吗?
- 为什么我的客户端不能正常显示汉字了?汉字全变成了"?"!help
你在http://community.csdn.net/Expert/topic/3280/3280668.xml?temp=.5988886
也犯了错误,你举的例子根本编译不通.slq server 中在
beginend
中只有一个select 语句行的,Oracle就不行。
beginend
中 只写
select * from aa;
在 Oracle 中这样写是没意义的,是错误的.
:
在oracle中是select * from dual;
我默认楼主的意思是:找出年龄在n1,n2 之间的人数时,年龄可以等于n1,但要小于n2,否则有的人要被计算2次了~
根据要求建立表ageverge:
SQL> select * from ageverge; BEGINAGE ENDAGE
---------- ----------
0 0
1 1
2 9
10 14
15 98sql>
create or replace procedure p1 as
total number(2);
v_beginage ageverge.BEGINAGE%type;
v_endage ageverge.ENDAGE%type;
cursor mycursor is
select beginage,endage from ageverge;
begin
open mycursor;
loop
fetch mycursor into v_beginage,v_endage;
exit when mycursor%notfound;
select count(age) into total from stu where age between v_beginage and v_endage;
dbms_output.PUT_LINE('the number of sutdents between '||v_beginage||' and '||v_endage||' is '||total);
end loop;
close mycursor;
end;
/过程已创建。SQL> execute p1;
the number of sutdents between 0 and 0 is 0
the number of sutdents between 1 and 1 is 2
the number of sutdents between 2 and 9 is 2
the number of sutdents between 10 and 14 is 0
the number of sutdents between 15 and 98 is 2PL/SQL 过程已成功完成。
IS
begin
null;
end;这个才是最简单的。楼上的写的过程是可以实现你的功能的
create or replace procedure
p1 (v_beginage ageverge.BEGINAGE%type;v_endage ageverge.ENDAGE%type;)as
total number(2);这样写比较好吧
---------- ----------
0 0
1 1
2 9
10 14
15 98create or replace view student_count_v
(from_age, to_age, student_count) as
select from_age, to_age, count(*)
from range x, student y
where y.age between x.from_age and x.to_age - 1;
比如我有表:
name age
小猛 2
小麦 3
小明 9
. .
. .
我想搜索到的结果为
年龄段 人数
0-1 4
1-2 7
. .
. .
sum(decode(sign(age-2),1,0,decode(sign(age-1),1,1,0)) as "1-2",
sum(decode(sign(age-10),1,0,decode(sign(age-2),1,1,0)) as "2-10",
sum(decode(sign(age-15),1,0,decode(sign(age-10),1,1,0)) as "10-15",
sum(decode(sign(age-15),1,1,0) as "15-"
from tablename
'2',sum(case age=2 then 1 else 0 end),
'3-10',sum(case age>2 or age<11 then 1 else 0 end),
'11-15',sum(case age>10 or age<16 then 1 else 0 end),
'16',sum(case age>15 then 1 else 0 end)
from tab_namem;
from tablename
where age between 0 and 1
union all
select '1-2',count(*)
from tablename
where age between 1 and 2
union all
select '2-10',count(*)
from tablename
where age between 2 and 10
union all
select '10-15',count(*)
from tablename
where age between 10 and 15
union all
select '15-',count(*)
from tablename
where age > 15
来解决年龄段没规律的问题,sql也简单多了。
所以建议用存储过程来实现。
(用一个sql也可以实现,但会很复杂)
is
cs_tmp t_refcur;
begin
open cs_tmp for ...; return cs_tmp;
end;
下面语句怎么写成存储过程
select '0-1',count(*)
from tablename
where age between 0 and 1
union all
select '1-2',count(*)
from tablename
where age between 1 and 2
union all
select '2-10',count(*)
from tablename
where age between 2 and 10
union all
select '10-15',count(*)
from tablename
where age between 10 and 15
union all
select '15-',count(*)
from tablename
where age > 15如果年龄端分了很多个这样select 语句那就更加长了。