求一个连接查询的写法?
创建实验环境表:
create   table   a(
code   int,
cname   varchar(10)
)
create   table   b(
zID   int   identity,
code   int,
iDate   datetime,
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, '2007-4-12 ',0.5)
insert   into   b   values(2, '2007-4-25 ',0.5)
insert   into   b   values(3, '2007-6-12 ',4)
insert   into   b   values(1, '2007-5-13 ',25)
insert   into   b   values(2, '2007-3-12 ',14)
insert   into   b   values(3, '2007-7-12 ',14)
insert   into   b   values(1, '2007-2-20 ',7)
insert   into   b   values(2, '2007-4-12 ',6)
insert   into   b   values(3, '2007-6-12 ',4)
insert   into   b   values(3, '2007-5-12 ',8)

我现在想得到的一个查询结果集的样式是
code,   cname,1月份天数,2月天数,...一直到12月，   天数合计


我这样写只能得到汇总的总和，怎么改一下呢？
select   a.code,m.days   from   a   left   join
(
select   code,days=sum(days)   from   b   group   by   code         --这里先把B表的资料汇总起来,然后
--实现和a表的查询...
)m   on   a.code=m.code

------解决方案--------------------
是不是要這樣的結果？

Select
a.code,
a.cname,
SUM(Case Month(iDate) When 1 Then 1 Else 0 End) As [1月份天数],
SUM(Case Month(iDate) When 2 Then 1 Else 0 End) As [2月份天数],
SUM(Case Month(iDate) When 3 Then 1 Else 0 End) As [3月份天数],
SUM(Case Month(iDate) When 4 Then 1 Else 0 End) As [4月份天数],
SUM(Case Month(iDate) When 5 Then 1 Else 0 End) As [5月份天数],
SUM(Case Month(iDate) When 6 Then 1 Else 0 End) As [6月份天数],
SUM(Case Month(iDate) When 7 Then 1 Else 0 End) As [7月份天数],
SUM(Case Month(iDate) When 8 Then 1 Else 0 End) As [8月份天数],
SUM(Case Month(iDate) When 9 Then 1 Else 0 End) As [9月份天数],
SUM(Case Month(iDate) When 10 Then 1 Else 0 End) As [10月份天数],
SUM(Case Month(iDate) When 11 Then 1 Else 0 End) As [11月份天数],
SUM(Case Month(iDate) When 12 Then 1 Else 0 End) As [12月份天数],
Count(*) As [天数合计]
From
a
Inner Join
b
On
A.code = B.code
Group By
a.code,
a.cname
------解决方案--------------------
create table a(
code int,
cname varchar(10)
)
create table b(
zID int identity,
code int,
iDate datetime,
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, '2007-4-12 ',0.5)
insert into b values(2, '2007-4-25 ',0.5)
insert into b values(3, '2007-6-12 ',4)
insert into b values(1, '2007-5-13 ',25)
insert into b values(2, '2007-3-12 ',14)
insert into b values(3, '2007-7-12 ',14)
insert into b values(1, '2007-2-20 ',7)
insert into b values(2, '2007-4-12 ',6)
insert into b values(3, '2007-6-12 ',4)
insert into b values(3, '2007-5-12 ',8)
GO
Select
a.code,
a.cname,
SUM(Case Month(iDate) When 1 Then 1 Else 0 End) As [1月份天数],
SUM(Case Month(iDate) When 2 Then 1 Else 0 End) As [2月份天数],