一个一对多关系的分组查询的一点问题?等
create table a(
code int,
cname varchar(10)
)
create table b(
zID int identity,
code int,
days float
)
insert into a values(1, 'aaa ')
insert into a values(2, 'bbb ')
insert into a values(3, 'ccc ')
insert into b values(1,0.5)
insert into b values(2,0.5)
insert into b values(3,4)
insert into b values(1,25)
insert into b values(2,14)
insert into b values(3,14)
insert into b values(1,7)
insert into b values(2,6)
insert into b values(3,4)
insert into b values(3,8)
表a和表b是一对多的关系,
现在想查询得到的结果是
a.code b.days
1 32.5
2 20.5
3 30
我写的语句怎么总是通不过撒
select a.*,b.[count] from a,(
select b.zID as JID,count(*) as [count] from a left join b on a.code=b.code
group by b.code
)b
[解决办法]
select a.code ,sum(b.days) as [days]
from a join b on a.code =b.code
group by a.code
[解决办法]
select a.code,m.days from a left join
(
select code,days=sum(days) from b group by code
)m on a.code=m.code
