有一个表是大概是这样的:
sn context groupname tm
1 ABCDE 1 2005-02-01
2 abc 1 2005-03-01
3 abc1 1 2005-03-01
4 abc2 0 2005-04-01
5 abc3 0 2005-04-01
6 abc4 1 2005-04-01
7 abc5 0 2005-05-01
现在想通个一个查询语句后得到下面的结果(nTemp1~12表示12个月)
groupname nTemp1 nTemp2 nTemp3 nTemp4 nTemp5 nTemp6 nTemp7 nTemp8 nTemp9 nTemp10 nTemp11 nTemp12
0 0 0 0 2 1 0 0 0 0 0 0 0
1 0 1 2 1 0 0 0 0 0 0 0 0请问这个查询语句怎么写啊?
sn context groupname tm
1 ABCDE 1 2005-02-01
2 abc 1 2005-03-01
3 abc1 1 2005-03-01
4 abc2 0 2005-04-01
5 abc3 0 2005-04-01
6 abc4 1 2005-04-01
7 abc5 0 2005-05-01
现在想通个一个查询语句后得到下面的结果(nTemp1~12表示12个月)
groupname nTemp1 nTemp2 nTemp3 nTemp4 nTemp5 nTemp6 nTemp7 nTemp8 nTemp9 nTemp10 nTemp11 nTemp12
0 0 0 0 2 1 0 0 0 0 0 0 0
1 0 1 2 1 0 0 0 0 0 0 0 0请问这个查询语句怎么写啊?
from monthly A
left join (select groupname,count(*) as temp1 from monthly where month(tm)=1 group by groupname) as B on A.groupname=B.groupname
left join (select groupname,count(*) as temp2 from monthly where month(tm)=2 group by groupname) as c on A.groupname=c.groupname
left join (select groupname,count(*) as temp3 from monthly where month(tm)=3 group by groupname) as d on A.groupname=d.groupname
left join (select groupname,count(*) as temp4 from monthly where month(tm)=4 group by groupname) as e on A.groupname=e.groupname
left join (select groupname,count(*) as temp5 from monthly where month(tm)=5 group by groupname) as f on A.groupname=f.groupname
left join (select groupname,count(*) as temp6 from monthly where month(tm)=6 group by groupname) as g on A.groupname=g.groupname
left join (select groupname,count(*) as temp7 from monthly where month(tm)=7 group by groupname) as h on A.groupname=h.groupname
left join (select groupname,count(*) as temp8 from monthly where month(tm)=8 group by groupname) as i on A.groupname=i.groupname
left join (select groupname,count(*) as temp9 from monthly where month(tm)=9 group by groupname) as j on A.groupname=j.groupname
left join (select groupname,count(*) as temp10 from monthly where month(tm)=10 group by groupname) as k on A.groupname=k.groupname
left join (select groupname,count(*) as temp11 from monthly where month(tm)=11 group by groupname) as l on A.groupname=l.groupname
left join (select groupname,count(*) as temp12 from monthly where month(tm)=12 group by groupname) as m on A.groupname=m.groupname
------------------------------------------------------
0 0 0 0 2 1 0 0 0 0 0 0 0
1 0 1 2 1 0 0 0 0 0 0 0 0