高手帮忙看看，已经几天了都没有解决,生成年报表，按stcode,pcode分组每月都要在报表里有，如果该月没有数据此显示-1

Question

CREATE PROCEDURE ESP 
@year int
AS
    select 
        a1.stcode,
        a1.pcode,
        ISNULL(avg(m1.mm1)  ,-1) '一月',
        ISNULL(avg(m2.mm2)  ,-1) '二月',
        ISNULL(avg(m3.mm3)  ,-1) '三月',
        ISNULL(avg(m4.mm4)  ,-1) '四月',
        ISNULL(avg(m5.mm5)  ,-1) '五月',
        ISNULL(avg(m6.mm6)  ,-1) '六月',
        ISNULL(avg(m7.mm7)  ,-1) '七月',
        ISNULL(avg(m8.mm8)  ,-1) '八月',
        ISNULL(avg(m9.mm9)  ,-1) '九月',
        ISNULL(avg(m10.mm10),-1) '十月',
        ISNULL(avg(m11.mm11),-1) '十一月',
        ISNULL(avg(m12.mm12),-1) '十二月' 
    from 
        a1
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm1 from  a1 where smo=1  GROUP BY stcode,pcode,ye) m1
    ON
        a1.stcode=m1.stcode and a1.pcode=m1.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm2 from  a1 where smo=2  GROUP BY stcode,pcode,ye) m2
    ON
        a1.stcode=m2.stcode and a1.pcode=m2.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm3 from  a1 where smo=3  GROUP BY stcode,pcode,ye) m3
    ON
        a1.stcode=m3.stcode and a1.pcode=m3.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm4 from  a1 where smo=4  GROUP BY stcode,pcode,ye) m4
    ON
        a1.stcode=m4.stcode and a1.pcode=m4.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm5 from  a1 where smo=5  GROUP BY stcode,pcode,ye) m5
    ON
        a1.stcode=m5.stcode and a1.pcode=m5.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm6 from  a1 where smo=6  GROUP BY stcode,pcode,ye) m6
    ON
        a1.stcode=m6.stcode and a1.pcode=m6.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm7 from  a1 where smo=7  GROUP BY stcode,pcode,ye) m7
    ON
        a1.stcode=m7.stcode and a1.pcode=m7.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm8 from  a1 where smo=8  GROUP BY stcode,pcode,ye) m8
    ON
        a1.stcode=m8.stcode and a1.pcode=m8.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm9 from  a1 where smo=9  GROUP BY stcode,pcode,ye) m9
    ON
        a1.stcode=m9.stcode and a1.pcode=m9.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm10 from a1 where smo=10 GROUP BY stcode,pcode,ye) m10
    ON
        a1.stcode=m10.stcode and a1.pcode=m10.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm11 from a1 where smo=11 GROUP BY stcode,pcode,ye) m11
    ON
        a1.stcode=m11.stcode and a1.pcode=m11.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm12 from a1 where smo=12 GROUP BY stcode,pcode,ye) m12
    ON
        a1.stcode=m12.stcode and a1.pcode=m12.pcode
    where 
        a1.ye=@year
    GROUP BY 
        a1.stcode,a1.pcode
GO

Answer 1

能不能在再最后一个记录加个统计~~~~
stcode,pcode,ye,一月,二月，三月，四月，五月，六月，七月，八月，九月，十月，十一月，十二月统计各个月份的总平均

Answer 2

CREATE PROCEDURE ESP 
@year int
AS
    select 
        a1.stcode,
        a1.pcode,
        ISNULL(avg(m1.mm1)  ,-1) '一月',
        ISNULL(avg(m2.mm2)  ,-1) '二月',
        ISNULL(avg(m3.mm3)  ,-1) '三月',
        ISNULL(avg(m4.mm4)  ,-1) '四月',
        ISNULL(avg(m5.mm5)  ,-1) '五月',
        ISNULL(avg(m6.mm6)  ,-1) '六月',
        ISNULL(avg(m7.mm7)  ,-1) '七月',
        ISNULL(avg(m8.mm8)  ,-1) '八月',
        ISNULL(avg(m9.mm9)  ,-1) '九月',
        ISNULL(avg(m10.mm10),-1) '十月',
        ISNULL(avg(m11.mm11),-1) '十一月',
        ISNULL(avg(m12.mm12),-1) '十二月' ,
        avg(a1.valuea)
    from 
        a1
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm1 from  a1 where smo=1  GROUP BY stcode,pcode,ye) m1
    ON
        a1.stcode=m1.stcode and a1.pcode=m1.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm2 from  a1 where smo=2  GROUP BY stcode,pcode,ye) m2
    ON
        a1.stcode=m2.stcode and a1.pcode=m2.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm3 from  a1 where smo=3  GROUP BY stcode,pcode,ye) m3
    ON
        a1.stcode=m3.stcode and a1.pcode=m3.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm4 from  a1 where smo=4  GROUP BY stcode,pcode,ye) m4
    ON
        a1.stcode=m4.stcode and a1.pcode=m4.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm5 from  a1 where smo=5  GROUP BY stcode,pcode,ye) m5
    ON
        a1.stcode=m5.stcode and a1.pcode=m5.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm6 from  a1 where smo=6  GROUP BY stcode,pcode,ye) m6
    ON
        a1.stcode=m6.stcode and a1.pcode=m6.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm7 from  a1 where smo=7  GROUP BY stcode,pcode,ye) m7
    ON
        a1.stcode=m7.stcode and a1.pcode=m7.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm8 from  a1 where smo=8  GROUP BY stcode,pcode,ye) m8
    ON
        a1.stcode=m8.stcode and a1.pcode=m8.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm9 from  a1 where smo=9  GROUP BY stcode,pcode,ye) m9
    ON
        a1.stcode=m9.stcode and a1.pcode=m9.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm10 from a1 where smo=10 GROUP BY stcode,pcode,ye) m10
    ON
        a1.stcode=m10.stcode and a1.pcode=m10.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm11 from a1 where smo=11 GROUP BY stcode,pcode,ye) m11
    ON
        a1.stcode=m11.stcode and a1.pcode=m11.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm12 from a1 where smo=12 GROUP BY stcode,pcode,ye) m12
    ON
        a1.stcode=m12.stcode and a1.pcode=m12.pcode
    where 
        a1.ye=@year
    GROUP BY 
        a1.stcode,a1.pcode
GO

Answer 3

上面是整年的平均，如果是12个月的平局可以这样：
CREATE PROCEDURE ESP 
@year int
AS
    select 
        a1.stcode,
        a1.pcode,
        ISNULL(avg(m1.mm1)  ,-1) '一月',
        ISNULL(avg(m2.mm2)  ,-1) '二月',
        ISNULL(avg(m3.mm3)  ,-1) '三月',
        ISNULL(avg(m4.mm4)  ,-1) '四月',
        ISNULL(avg(m5.mm5)  ,-1) '五月',
        ISNULL(avg(m6.mm6)  ,-1) '六月',
        ISNULL(avg(m7.mm7)  ,-1) '七月',
        ISNULL(avg(m8.mm8)  ,-1) '八月',
        ISNULL(avg(m9.mm9)  ,-1) '九月',
        ISNULL(avg(m10.mm10),-1) '十月',
        ISNULL(avg(m11.mm11),-1) '十一月',
        ISNULL(avg(m12.mm12),-1) '十二月' ,
        (ISNULL(avg(m1.mm1)  ,0)+ISNULL(avg(m2.mm2),0)+ISNULL(avg(m3.mm3),0)+ISNULL(avg(m4.mm4),0)+ISNULL(avg(m5.mm5),0)+ISNULL(avg(m6.mm6),0)+ISNULL(avg(m7.mm7),0)+ISNULL(avg(m8.mm8),0)+ISNULL(avg(m9.mm9),0)+ISNULL(avg(m10.mm10),0)+ISNULL(avg(m11.mm11),0)+ISNULL(avg(m12.mm12),0))/12 as 12个月平均
        from 
        a1
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm1 from  a1 where smo=1  GROUP BY stcode,pcode,ye) m1
    ON
        a1.stcode=m1.stcode and a1.pcode=m1.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm2 from  a1 where smo=2  GROUP BY stcode,pcode,ye) m2
    ON
        a1.stcode=m2.stcode and a1.pcode=m2.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm3 from  a1 where smo=3  GROUP BY stcode,pcode,ye) m3
    ON
        a1.stcode=m3.stcode and a1.pcode=m3.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm4 from  a1 where smo=4  GROUP BY stcode,pcode,ye) m4
    ON
        a1.stcode=m4.stcode and a1.pcode=m4.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm5 from  a1 where smo=5  GROUP BY stcode,pcode,ye) m5
    ON
        a1.stcode=m5.stcode and a1.pcode=m5.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm6 from  a1 where smo=6  GROUP BY stcode,pcode,ye) m6
    ON
        a1.stcode=m6.stcode and a1.pcode=m6.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm7 from  a1 where smo=7  GROUP BY stcode,pcode,ye) m7
    ON
        a1.stcode=m7.stcode and a1.pcode=m7.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm8 from  a1 where smo=8  GROUP BY stcode,pcode,ye) m8
    ON
        a1.stcode=m8.stcode and a1.pcode=m8.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm9 from  a1 where smo=9  GROUP BY stcode,pcode,ye) m9
    ON
        a1.stcode=m9.stcode and a1.pcode=m9.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm10 from a1 where smo=10 GROUP BY stcode,pcode,ye) m10
    ON
        a1.stcode=m10.stcode and a1.pcode=m10.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm11 from a1 where smo=11 GROUP BY stcode,pcode,ye) m11
    ON
        a1.stcode=m11.stcode and a1.pcode=m11.pcode
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm12 from a1 where smo=12 GROUP BY stcode,pcode,ye) m12
    ON
        a1.stcode=m12.stcode and a1.pcode=m12.pcode
    where 
        a1.ye=@year
    GROUP BY 
        a1.stcode,a1.pcode
GO

Answer 4

能不能把每个月的平均做为最后一条记录
stcode,pcode,ye,一月,二月，三月，四月，五月，六月，七月，八月，九月，十月，十一月，十二月
统计,,年,一月的平均,二月的平均......................

Answer 5

CREATE PROCEDURE ESP 
@year int
AS
    select 
        a1.stcode,
        a1.pcode,
        a1.ye,
        ISNULL(avg(m1.mm1)  ,-1) '一月',
        ISNULL(avg(m2.mm2)  ,-1) '二月',
        ISNULL(avg(m3.mm3)  ,-1) '三月',
        ISNULL(avg(m4.mm4)  ,-1) '四月',
        ISNULL(avg(m5.mm5)  ,-1) '五月',
        ISNULL(avg(m6.mm6)  ,-1) '六月',
        ISNULL(avg(m7.mm7)  ,-1) '七月',
        ISNULL(avg(m8.mm8)  ,-1) '八月',
        ISNULL(avg(m9.mm9)  ,-1) '九月',
        ISNULL(avg(m10.mm10),-1) '十月',
        ISNULL(avg(m11.mm11),-1) '十一月',
        ISNULL(avg(m12.mm12),-1) '十二月' ,
        (ISNULL(avg(m1.mm1)  ,0)+ISNULL(avg(m2.mm2),0)+ISNULL(avg(m3.mm3),0)+ISNULL(avg(m4.mm4),0)+ISNULL(avg(m5.mm5),0)+ISNULL(avg(m6.mm6),0)+ISNULL(avg(m7.mm7),0)+ISNULL(avg(m8.mm8),0)+ISNULL(avg(m9.mm9),0)+ISNULL(avg(m10.mm10),0)+ISNULL(avg(m11.mm11),0)+ISNULL(avg(m12.mm12),0))/12 as 12个月平均
        from 
        a1
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm1 from  a1 where smo=1  GROUP BY stcode,pcode,ye) m1
    ON
        a1.stcode=m1.stcode and a1.pcode=m1.pcode and a1.ye=m1.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm2 from  a1 where smo=2  GROUP BY stcode,pcode,ye) m2
    ON
        a1.stcode=m2.stcode and a1.pcode=m2.pcode and a1.ye=m2.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm3 from  a1 where smo=3  GROUP BY stcode,pcode,ye) m3
    ON
        a1.stcode=m3.stcode and a1.pcode=m3.pcode and a1.ye=m3.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm4 from  a1 where smo=4  GROUP BY stcode,pcode,ye) m4
    ON
        a1.stcode=m4.stcode and a1.pcode=m4.pcode and a1.ye=m4.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm5 from  a1 where smo=5  GROUP BY stcode,pcode,ye) m5
    ON
        a1.stcode=m5.stcode and a1.pcode=m5.pcode and a1.ye=m5.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm6 from  a1 where smo=6  GROUP BY stcode,pcode,ye) m6
    ON
        a1.stcode=m6.stcode and a1.pcode=m6.pcode and a1.ye=m6.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm7 from  a1 where smo=7  GROUP BY stcode,pcode,ye) m7
    ON
        a1.stcode=m7.stcode and a1.pcode=m7.pcode and a1.ye=m7.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm8 from  a1 where smo=8  GROUP BY stcode,pcode,ye) m8
    ON
        a1.stcode=m8.stcode and a1.pcode=m8.pcode and a1.ye=m8.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm9 from  a1 where smo=9  GROUP BY stcode,pcode,ye) m9
    ON
        a1.stcode=m9.stcode and a1.pcode=m9.pcode and a1.ye=m9.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm10 from a1 where smo=10 GROUP BY stcode,pcode,ye) m10
    ON
        a1.stcode=m10.stcode and a1.pcode=m10.pcode and a1.ye=m10.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm11 from a1 where smo=11 GROUP BY stcode,pcode,ye) m11
    ON
        a1.stcode=m11.stcode and a1.pcode=m11.pcode and a1.ye=m11.ye
    LEFT JOIN
        (select stcode,pcode,ye,avg(valuea) mm12 from a1 where smo=12 GROUP BY stcode,pcode,ye) m12
    ON
        a1.stcode=m12.stcode and a1.pcode=m12.pcode anda1.ye=m12.ye
    GROUP BY 
        a1.stcode,a1.pcode,a1.ye
GO