表A（应付款明细）有A1(年月日)，A2(供应商编号)，A3(发票类型)，A4(应付金额)列数据如下：

A1         A2      A3      A4
20141102   zz1     Y       800
20141115   zz1     Y       999
20141116   zz1     N       444
20141202   zz1     Y       555
20141127   as1     Y       333
20141127   as1     Y       222
20141129   as1     N       110
20141210   as1     Y       800

表B（已付款明细）有B1(年月日)，B2(供应商编号)，B3(已付款金额)列数据如下：

B1         B2      B3
20141105   zz1     100
20141128   zz1     200
20141214   zz1     300
20141130   as1     150
20141219   as1     300
20141226   as1     200


需求结果如下

Y1    201411月   201412月   应付款合计   201411月   201412月   已付款合计   未付款合计  全年已付款比率(%)
zz1   1355       555        1910         300        300        600          1310        31.41
as1   445        800        1245         150        500        650          595         52.21


A表与B表由A2=B2进行关联
应付款合计列：按月统计（当A3列为Y时将A4列数据相加）-（当A3列为N时将A4列数据相加）
已付款合计列：全计已付款金额合计
未付款合计列：应付款合计列 - 已付款合计列
全年已付款比率列：已付款合计列 / 应付款合计
以上只是列出11与12月份的应付款
报表需可按某个月，某几个月进查询

好复杂.哈哈
我是来攒经验的..

--建表
Create table 表A
(
 A1 datetime,
 A2 varchar(10),
 A3 varchar(10),
 A4 int
)

Create Table 表B
(
B1 datetime,
B2 varchar(10),
B3 int
)

--插入数据
insert into 表A values('20141102','zz1','Y',800)
insert into 表A values('20141115','zz1','Y',999)
insert into 表A values('20141116','zz1','N',444)
insert into 表A values('20141202','zz1','Y',555)
insert into 表A values('20141127','as1','Y',333)
insert into 表A values('20141127','as1','Y',222)
insert into 表A values('20141129','as1','N',110)
insert into 表A values('20141210','as1','Y',800)
insert into 表A values('20141010','as1','Y',300)
insert into 表A values('20141010','zz1','Y',400)

insert into 表B values('20141105','zz1',100)
insert into 表B values('20141128','zz1',200)
insert into 表B values('20141214','zz1',300)
insert into 表B values('20141130','as1',150)
insert into 表B values('20141219','as1',300)
insert into 表B values('20141226','as1',200)
insert into 表B values('20141026','as1',200)
insert into 表B values('20141026','zz1',200)

--创建存储过程(你的结果需要动态行列转换)
Create Procedure sp_GetBaobiao(@StartDT datetime,@EndDT datetime)
As
Begin
    /*
        思路
        1.把表A,表B某个时间段的数据保存到临时表
        2.把两个临时表的日期转换位年月,并拼成字符串,如@[201411月],@[201412月]这样,这里加了@是后面需要加限定符
        3.把临时表按年月汇总后,做行列转换(这里的年月是动态的),再按A2=B2做连接
        4.计算未付款合计和全年已付款比率%
        搞定
    */

    Declare @Sql Varchar(8000)
    Declare @F Varchar(1000)=''
   
    Select * Into #表A From 表A Where A1 Between @StartDT And @EndDT  
    Select * Into #表B From 表B Where B1 Between @StartDT And @EndDT
   
    Select @F=@F+'@['+A1+'],' From
    (
        Select Convert(Varchar(6),A1,112)+'月' As A1 From #表A
        union
        Select Convert(Varchar(6),B1,112)+'月' As A1 From #表B
    )S
   
    Set @F=STUFF(@F,LEN(@F),1,'')
   
    Set @SQL='Select M.A2,'+replace(@F,'@','M.')+',应付款合计,'+replace(@F,'@','N.')+',
    应付款合计-已付款合计 As 未付款合计,
    Round(100.0*已付款合计/应付款合计,2) as [全年已付款比率%]
    From
    (
        Select *,(Select Sum(Case When A3=''Y'' Then 1 Else -1 End*A4) From
            #表A Where A2=pvt.A2) As 应付款合计
        From
        (
            Select Convert(Varchar(6),A1,112)+''月'' As Y1,A2,
                Sum(Case When A3=''Y'' Then 1 Else -1 End*A4) A4 From #表A
                    Group by Convert(Varchar(6),A1,112),A2
        ) A
        Pivot
        (
            Sum(A4)
            For Y1 in('+replace(@F,'@','')+')
        )As pvt
    ) M
    Left Join
    (
        Select *,(Select Sum(B3) From #表B Where B2=pvt.B2) As 已付款合计
        From
        (
            Select Convert(Varchar(6),B1,112)+''月'' As Y1,B2,Sum(B3) As B3 From #表B
                Group by Convert(Varchar(6),B1,112),B2
        ) A
        Pivot
        (
            Sum(B3)
            For Y1 in('+replace(@F,'@','')+')
        )As pvt
    ) N on M.A2=N.B2'
   
    Exec (@sql)
    Drop Table #表A
    Drop Table #表B
End

--得到结果,开始日期到结束日期(必须是同一年,否则全年已付款比率列不正确)
Exec sp_GetBaobiao '20141001','20141230'

--效果


[ 本帖最后由 mxbing1984 于 2015-3-3 11:53 编辑 ]