四表联合组成交叉表问题,请高手指教~~

Question

declare @sql varchar(8000)set @sql='select 业务员'
select @sql=@sql+'case when 年月='''+t2.年月+''' and 类别名称='''+ t3.类别名称 +''' then 量 else 0 end as ['+rtrim(t2.年月)+rtrim(t3.类别名称)+']'
from 
(
select distinct datename(year,日期)+'年'+datename(year,日期)+月 as 年月
from (
select 发票日期 as 日期 from 销售表
union all
select 收款日期 from 收款单
) as t1
) as t2,
(
select * from 类别表 
union all 
select 'zzzzzzzzzzzzz1' as 编号,'总数量' as 类别名称
union all 
select 'zzzzzzzzzzzzz2' as 编号,'总销售额' as 类别名称
union all 
select 'zzzzzzzzzzzzz2' as 编号,'总回笼额' as 类别名称
) as t3
order by t2.年月,t3.编号exec(@sql+' from (
select datename(year,x.发票日期)+'年'+datename(year,x.发票日期)+月 as 年月,l.类别名称,x.数量 as 量,x.业务员 from 销售表 x,类别表 l,产品表 c
where c.产品名=x.产品名 and c.所属类型=l.编号
union all
select datename(year,发票日期)+'年'+datename(year,发票日期)+月 as 年月,'总数量' as 类别名称,数量 as 量,业务员 from 销售表 
union all
select datename(year,发票日期)+'年'+datename(year,发票日期)+月 as 年月,'总销售额' as 类别名称,价格 as 量,业务员 from 销售表 
union all
select datename(year,收款日期)+'年'+datename(year,收款日期)+月 as 年月,'总回笼额' as 类别名称,金额 as 量,收款人 as 业务员 from 收款单
) as t4
group by 业务员')
--没有测试，几乎不可能没有错，希望楼主能理解算法，自己修改正确

Answer 1

马上发现错误，改正如下：declare @sql varchar(8000)set @sql='select 业务员'
select @sql=@sql+',case when 年月='''+rtrim(t2.年月)+''' and 类别名称='''+ rtrim(t3.类别名称) +''' then 量 else 0 end as ['+rtrim(t2.年月)+rtrim(t3.类别名称)+']'
from 
(
select distinct datename(year,日期)+'年'+datename(year,日期)+月 as 年月
from (
select 发票日期 as 日期 from 销售表
union all
select 收款日期 from 收款单
) as t1
) as t2,
(
select * from 类别表 
union all 
select 'zzzzzzzzzzzzz1' as 编号,'总数量' as 类别名称
union all 
select 'zzzzzzzzzzzzz2' as 编号,'总销售额' as 类别名称
union all 
select 'zzzzzzzzzzzzz2' as 编号,'总回笼额' as 类别名称
) as t3
order by t2.年月,t3.编号exec(@sql+' from (
select datename(year,x.发票日期)+'年'+datename(year,x.发票日期)+月 as 年月,l.类别名称,x.数量 as 量,x.业务员 from 销售表 x,类别表 l,产品表 c
where c.产品名=x.产品名 and c.所属类型=l.编号
union all
select datename(year,发票日期)+'年'+datename(year,发票日期)+月 as 年月,'总数量' as 类别名称,数量 as 量,业务员 from 销售表 
union all
select datename(year,发票日期)+'年'+datename(year,发票日期)+月 as 年月,'总销售额' as 类别名称,价格 as 量,业务员 from 销售表 
union all
select datename(year,收款日期)+'年'+datename(year,收款日期)+月 as 年月,'总回笼额' as 类别名称,金额 as 量,收款人 as 业务员 from 收款单
) as t4
group by 业务员')

Answer 2

晕，错的都是关键的：declare @sql varchar(8000)set @sql='select 业务员'
select @sql=@sql+',sum(case when 年月='''+rtrim(t2.年月)+''' and 类别名称='''+ rtrim(t3.类别名称) +''' then 量 else 0 end) as ['+rtrim(t2.年月)+rtrim(t3.类别名称)+']'
from 
(
select distinct datename(year,日期)+'年'+datename(year,日期)+月 as 年月
from (
select 发票日期 as 日期 from 销售表
union all
select 收款日期 from 收款单
) as t1
) as t2,
(
select * from 类别表 
union all 
select 'zzzzzzzzzzzzz1' as 编号,'总数量' as 类别名称
union all 
select 'zzzzzzzzzzzzz2' as 编号,'总销售额' as 类别名称
union all 
select 'zzzzzzzzzzzzz2' as 编号,'总回笼额' as 类别名称
) as t3
order by t2.年月,t3.编号exec(@sql+' from (
select datename(year,x.发票日期)+'年'+datename(year,x.发票日期)+月 as 年月,l.类别名称,x.数量 as 量,x.业务员 from 销售表 x,类别表 l,产品表 c
where c.产品名=x.产品名 and c.所属类型=l.编号
union all
select datename(year,发票日期)+'年'+datename(year,发票日期)+月 as 年月,'总数量' as 类别名称,数量 as 量,业务员 from 销售表 
union all
select datename(year,发票日期)+'年'+datename(year,发票日期)+月 as 年月,'总销售额' as 类别名称,价格 as 量,业务员 from 销售表 
union all
select datename(year,收款日期)+'年'+datename(year,收款日期)+月 as 年月,'总回笼额' as 类别名称,金额 as 量,收款人 as 业务员 from 收款单
) as t4
group by 业务员')

Answer 3

--生成数据
create table 类别表(
编号     varchar(10),
类别名称 varchar(10))
insert into 类别表 select 'a','家禽类'
insert into 类别表 select 'b','肉类'
insert into 类别表 select 'c','蔬菜类'
insert into 类别表 select 'd','豆类'create table 产品表(
编号      int,
产品名    varchar(10),
所属类型  varchar(10))
insert into 产品表 select 1,'鸡'    ,'a'
insert into 产品表 select 2,'猪肉'  ,'b'
insert into 产品表 select 3,'大白菜','c'
insert into 产品表 select 4,'玉米'  ,'c'
insert into 产品表 select 5,'黄豆'  ,'d'
create table 销售表(
发票号   int,
发票日期 datetime,
产品名   varchar(10),
数量     int,
价格     money,
业务员   varchar(10))
insert into 销售表 select 1 ,'2004.03.08','鸡'    ,20 , 100.00,'王'
insert into 销售表 select 2 ,'2004.03.10','猪肉'  ,30 ,1500.00,'李'
insert into 销售表 select 3 ,'2004.03.14','玉米'  ,40 , 800.00,'陈'
insert into 销售表 select 4 ,'2004.03.15','大白菜',50 , 250.00,'王'
insert into 销售表 select 5 ,'2004.03.18','黄豆'  ,60 , 432.00,'王'
insert into 销售表 select 6 ,'2005.03.08','鸡'    ,70 , 100.00,'王'
insert into 销售表 select 7 ,'2005.03.10','猪肉'  ,80 ,1500.00,'李'
insert into 销售表 select 8 ,'2005.03.14','玉米'  ,90 , 800.00,'陈'
insert into 销售表 select 9 ,'2005.03.15','大白菜',100, 250.00,'王'
insert into 销售表 select 10,'2005.03.18','黄豆'  ,110, 432.00,'王'create table 收款单(
收款日期 datetime,
收款人   varchar(10),
金额     money)
insert into 收款单 select '2004.03.25','王',400.00
insert into 收款单 select '2004.03.19','李',600.00
insert into 收款单 select '2004.03.31','陈',800.00
insert into 收款单 select '2005.03.25','王',300.00
insert into 收款单 select '2005.03.19','李',500.00
insert into 收款单 select '2005.03.31','陈',700.00
--执行查询
DECLARE @s VARCHAR(8000),@s1 VARCHAR(4000),@date CHAR(7)
SET @s = ''
SET @s1= ''DECLARE T_cursor CURSOR FOR 
SELECT DISTINCT CONVERT(CHAR(7),发票日期,120) FROM 销售表OPEN T_cursorFETCH NEXT FROM T_cursor INTO @dateWHILE @@FETCH_STATUS = 0
BEGIN
    SELECT 
        @s = @s + ',['+@date+类别名称+']=SUM(CASE WHEN CONVERT(CHAR(7),发票日期,120)='''+@date+''' AND 所属类型='''+编号+''' THEN 数量 END)',
        @s1= @s1+ ',['+@date+类别名称+']=SUM(CASE WHEN CONVERT(CHAR(7),发票日期,120)='''+@date+''' AND 所属类型='''+编号+''' THEN 数量 END)'
    FROM 类别表
    
    SET @s = @s + ',[' +@date+'总销售额]=SUM(CASE WHEN CONVERT(CHAR(7),发票日期,120)='''+@date+''' THEN 价格 END)'
    SET @s1= @s1+ ',[' +@date+'总销售额]=SUM(CASE WHEN CONVERT(CHAR(7),发票日期,120)='''+@date+''' THEN 价格 END)'
    SET @s = @s + ',[' +@date+'总回笼额]=(SELECT SUM(金额) FROM 收款单 WHERE 收款人=A.业务员 AND CONVERT(CHAR(7),收款日期,120)='''+@date+''')'
    SET @s1= @s1+ ',[' +@date+'总回笼额]=(SELECT SUM(金额) FROM 收款单 WHERE CONVERT(CHAR(7),收款日期,120)='''+@date+''')'
    
    FETCH NEXT FROM T_cursor INTO @date
END
CLOSE T_cursor
DEALLOCATE T_cursorSET @s = 'SELECT a.业务员' + @s + ' FROM (SELECT b.所属类型,c.* FROM 产品表 b,销售表 c WHERE b.产品名 = c.产品名) a GROUP BY a.业务员'
       + ' UNION ALL '
       + 'SELECT ''合计'''+@s1+' FROM (SELECT b.所属类型,c.* FROM 产品表 b,销售表 c WHERE b.产品名 = c.产品名) d'EXEC(@s)