幫忙寫sql語句
各位，請幫忙寫條sql語句 數句如下:
表1
sno name
11 胡偉
22 賀正生
33 張三
表2
sno cno
11 c1
11 c2
22 c4
33 c4
要求顯示結果如下
name sum
胡偉 2
賀正生 1
張三 1
[解决办法]
select [name]=a.name,[cno]=sum(count(b.cno)) from 表1 a,表1 b
where a.sno=b.cno
group by a.name
[解决办法]
select a1.name,count(*) from 表1 a1,表2 a2 where a1.sno=a2.sno group by a1.name;
[解决办法]
create table a(sno int,name varchar(10))
insert a select 11, '胡伟 '
union all select 22, '賀正生 '
union all select 33, '張三 '
create table b(sno int,cno varchar(10))
insert b select 11, 'c1 '
union all select 11, 'c2 '
union all select 22, 'c4 '
union all select 33, 'c4 '
select name,[sum]
from a,
(select sno,[sum]=count(1)
from b
group by b.sno) x
where a.sno=x.sno
drop table a
