a,b 表没有关系 select substr(name,1,1),count(1) from a group by substr(name,1,1);好像不可以吧 我的意思比如说a表name字段中 有“王××”的记录20条,那么统计出来就是20,但是要把比表中所有的姓都要有个统计
select count(*),b.name from a,b where substr(a.name,1,1) = b.name group by b.name;
select substr(name,1,1),count(1) from a b where substr(name,1,1)(+)=b.姓 group by substr(name,1,1),b.姓;
select count(*),b.name from a,b where substr(a.name,1,1) = b.name group by b.name; 复姓怎么办?
如果姓名有两个字则不行,还是得用到b表,根据b表有的姓 来做判断后 GROUP BY
建一个姓表 a(name,a_id)姓名,姓编码 b(b_id,name)编码,姓create trigger a_tri before insert on a for each row begin for i in 1..10 loop for c in (select b_id,name from b) loop if substr(:new.name,i,1)=c.name then :new.a_id:=c.b_id; goto next_label; end if; end loop; end loop; <<next_label>> null; end; /select count(1),(select name from b where b_id=a_id) from a group by a_id;
如果姓名有两个字则不行,还是得用到b表,根据b表有的姓 来做判断后 GROUP BY <<<<<<<<<<<<<<<<<<<<<<< 但是姓可能会有很多种情况(这边用姓名只是举个例子好说点),用group by可能就麻烦点
建一个姓表 a(name,a_id)姓名,姓编码 b(b_id,name)编码,姓create trigger a_tri before insert on a for each row begin for i in 1..10 loop for c in (select b_id,name from b) loop if substr(:new.name,i,1)=c.name then :new.a_id:=c.b_id; goto next_label; end if; end loop; end loop; <<next_label>> null; end; /select count(1),(select name from b where b_id=a_id) from a group by a_id; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 好像一样不可以解决复姓的问题
select substr(name,1,1),count(1) from a group by substr(name,1,1);好像不可以吧
我的意思比如说a表name字段中 有“王××”的记录20条,那么统计出来就是20,但是要把比表中所有的姓都要有个统计
复姓怎么办?
来做判断后 GROUP BY
a(name,a_id)姓名,姓编码
b(b_id,name)编码,姓create trigger a_tri
before insert on a
for each row
begin
for i in 1..10 loop
for c in (select b_id,name from b) loop
if substr(:new.name,i,1)=c.name then
:new.a_id:=c.b_id;
goto next_label;
end if;
end loop;
end loop;
<<next_label>>
null;
end;
/select count(1),(select name from b where b_id=a_id) from a group by a_id;
来做判断后 GROUP BY
<<<<<<<<<<<<<<<<<<<<<<<
但是姓可能会有很多种情况(这边用姓名只是举个例子好说点),用group by可能就麻烦点
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
就是在一个表一样不能解决这个问题啊
a(name,a_id)姓名,姓编码
b(b_id,name)编码,姓create trigger a_tri
before insert on a
for each row
begin
for i in 1..10 loop
for c in (select b_id,name from b) loop
if substr(:new.name,i,1)=c.name then
:new.a_id:=c.b_id;
goto next_label;
end if;
end loop;
end loop;
<<next_label>>
null;
end;
/select count(1),(select name from b where b_id=a_id) from a group by a_id;
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
好像一样不可以解决复姓的问题