select name, (select count(*) from a where a.name like b.name||'%') from b 假设b有5行,每行的name不同,按上面写的式子是无法让数据库根据b.name计算出各自的总数,得到的结果只会是同样的值 不知道要写实现上面的功能,需要如何处理或修改,谢谢
select b.name,count(*) from b ,a where instr(a.name,b.name)>0 group by b.name--orselect name, (select count(*) from a where instr(a.name,b.name)>0 ) from b
你这个需求是不是 找出 在a表中 name like B表中的name 的个数,然后 拼接 b.name cursor cur_name is select name from b; rec_name cur_name%rowtype;cursor cur_B_count(v_name varchar2) is select v_name,count(1) from a where a.name like v_name||'%' group by v_name; rec_B_count cur_B_count%rowtype;然后就一条一条取了,先打开 cur_name 游标,然后 在 里面打开 Cur_B_count(rec_name.name);
其实 b 游标可以 不要select name 了, 就是 select count(1) from a where a.name like v_name ||'%';
--没有问题啊 SQL> with b as ( 2 select 'a' name from dual union all 3 select 'b' name from dual union all 4 select 'c' name from dual union all 5 select 'd' name from dual union all 6 select 'e' name from dual), 7 a as ( 8 select 'aa' name from dual union all 9 select 'eb' name from dual union all 10 select 'cc' name from dual union all 11 select 'ae' name from dual union all 12 select 'ef' name from dual union all 13 select 'dk' name from dual) 14 SELECT b.NAME, (SELECT COUNT(*) FROM a WHERE a.name LIKE b.name || '%') CNT 15 FROM b;N CNT - ---------- a 2 b 0 c 1 d 1 e 2SQL>
from b ,a
where instr(a.name,b.name)>0
group by b.name--orselect name,
(select count(*) from a where instr(a.name,b.name)>0 )
from b
cursor cur_name is
select name from b;
rec_name cur_name%rowtype;cursor cur_B_count(v_name varchar2) is
select v_name,count(1) from a
where a.name like v_name||'%'
group by v_name;
rec_B_count cur_B_count%rowtype;然后就一条一条取了,先打开 cur_name 游标,然后 在 里面打开 Cur_B_count(rec_name.name);
SQL> with b as (
2 select 'a' name from dual union all
3 select 'b' name from dual union all
4 select 'c' name from dual union all
5 select 'd' name from dual union all
6 select 'e' name from dual),
7 a as (
8 select 'aa' name from dual union all
9 select 'eb' name from dual union all
10 select 'cc' name from dual union all
11 select 'ae' name from dual union all
12 select 'ef' name from dual union all
13 select 'dk' name from dual)
14 SELECT b.NAME, (SELECT COUNT(*) FROM a WHERE a.name LIKE b.name || '%') CNT
15 FROM b;N CNT
- ----------
a 2
b 0
c 1
d 1
e 2SQL>