这样的报表SQL如何生成? UP 有分

[bitzhw]

表1：工序表
工序编号 工序名称 工序类型
1001 工序一 外加工
1002 工序二 内加工


表2：产品部件表
编号 产品款号 部件编号 部件名称
1 AB AB01 部件一
2 AB AB02 部件二

表3：工序单价表
工序报价编号 产品部件编号 工序编号 单价
1 1 1001 100
2 1 1002 200
3 2 1001 1000
4 2 1002 2000


生成报表：

产品款号(AB) 部件一 部件二 合计

工序一 100 1000 1100
工序二 200 2000 2200
合 计 300 3000 3300


SQL怎样写？

[bitzhw]

楼上兄台牛，先谢了

测试过后，还有一点问题：

我加上其它产品款号，部件，统计就出问题了

insert into 工序表 select 1003,'工序3','内加工'
insert into 产品部件表 select 3,'AC','AC01','部件一'
insert into 工序单价表 select 5,2,1003,2000
insert into 工序单价表 select 5,3,1003,2000

然后统计： 产品款号为AC的 ，出来的统计单价就出错了。

我试着改了几处，都不行，可能对这种交叉报表不熟悉吧，麻烦帮我再看看！

[LYSSHE]

UP，我不了解。
有空的朋友可以看看我的问题，创建连接数据库。
http://community.csdn.net/Expert/topic/4146/4146070.xml?temp=.709881

[子陌红尘]

修正如下：
---------------------------------------------------------------------------------
--生成测试数据
create table 工序表(工序编号 int,工序名称 varchar(20),工序类型 varchar(20))
insert into 工序表 select 1001,'工序一','外加工'
insert into 工序表 select 1002,'工序二','内加工'
insert into 工序表 select 1003,'工序三','内加工'
create table 产品部件表(编号 int,产品款号 varchar(20),部件编号 varchar(20),部件名称 varchar(20))
insert into 产品部件表 select 1,'AB','AB01','部件一'
insert into 产品部件表 select 2,'AB','AB02','部件二'
insert into 产品部件表 select 3,'AC','AC01','部件一'
create table 工序单价表(工序报价编号 int,产品部件编号 int,工序编号 int,单价 int)
insert into 工序单价表 select 1,1,1001,100
insert into 工序单价表 select 2,1,1002,200
insert into 工序单价表 select 3,2,1001,1000
insert into 工序单价表 select 4,2,1002,2000
insert into 工序单价表 select 5,2,1003,2000
insert into 工序单价表 select 5,3,1003,2000

--利用动态SQL实现交叉表查询
declare @s varchar(8000),@v varchar(8000)
set @s = ''
set @v = ''

select
@s = @s + ','+部件名称+'=sum(case when 产品部件编号='+rtrim(编号)+' then 单价 end)',
@v = @v + ','+部件名称
from
产品部件表
where
产品款号 = 'AB'
order by
编号

set @s = 'select a.工序编号,a.工序名称'+@s+',合计=sum(单价) from 工序表 a,工序单价表 b where a.工序编号 = b.工序编号 and b.产品部件编号 in(select 编号 from 产品部件表 where 产品款号 = ''AB'' ) group by a.工序编号,a.工序名称 with rollup'
set @v = 'select [产品款号(AB)] = isnull(a.工序名称,''合计'')'+@v+',合计 from('+@s+') a where a.工序编号 is null or a.工序名称 is not null order by case when a.工序编号 is null then 2 else 1 end,a.工序编号'

exec(@v)


--输出结果
产品款号(AB) 部件一 部件二 合计
------------ ------- ------ ------
工序一 100 1000 1100
工序二 200 2000 2200
工序三 NULL 2000 2000
合 计 300 5000 5300

[bitzhw]

还不行，兄台统计一下产品款号(AC)的，你就知道问题所在的了，
统计出来的不是产品款号为AC的

麻烦了

[bitzhw]

生成这样的

--输出结果
产品款号(AC) 部件一 部件二 合计
------------ ------- ------ ------
工序一 NULL 1100
工序二 NULL 2200
工序3 NULL 2000
合计 NULL 5300

[子陌红尘]

--利用动态SQL实现交叉表查询
declare @s varchar(8000),@v varchar(8000)
set @s = ''
set @v = ''

select
@s = @s + ','+部件名称+'=sum(case when 产品部件编号='+rtrim(编号)+' then 单价 end)',
@v = @v + ','+部件名称
from
产品部件表
where
产品款号 = 'AC'
order by
编号

set @s = 'select a.工序编号,a.工序名称'+@s+',合计=sum(单价) from 工序表 a,工序单价表 b where a.工序编号 = b.工序编号 and b.产品部件编号 in(select 编号 from 产品部件表 where 产品款号 = ''AC'' ) group by a.工序编号,a.工序名称 with rollup'
set @v = 'select [产品款号(AC)] = isnull(a.工序名称,''合计'')'+@v+',合计 from('+@s+') a where a.工序编号 is null or a.工序名称 is not null order by case when a.工序编号 is null then 2 else 1 end,a.工序编号'

exec(@v)

--输出结果
产品款号(AC) 部件一 合计
------------ ------- ------
工序三 2000 2000
合计 2000 2000

[mengzulin]

up

[子陌红尘]

生成这样的

--输出结果
产品款号(AC) 部件一 部件二 合计
------------ ------- ------ ------
工序一 NULL 1100
工序二 NULL 2200
工序3 NULL 2000
合计 NULL 5300

----------------------------------------------------------

楼主的意思是当产品款号为AC时，需要生成楼上格式的输出结果？

[bitzhw]

兄台，行了

你一并到
http://community.csdn.net/Expert/TopicView.asp?id=4144378

结账送分了

[mengzulin]

up

[子陌红尘]

--生成测试数据
create table 工序表(工序编号 int,工序名称 varchar(20),工序类型 varchar(20))
insert into 工序表 select 1001,'工序一','外加工'
insert into 工序表 select 1002,'工序二','内加工'
create table 产品部件表(编号 int,产品款号 varchar(20),部件编号 varchar(20),部件名称 varchar(20))
insert into 产品部件表 select 1,'AB','AB01','部件一'
insert into 产品部件表 select 2,'AB','AB02','部件二'
create table 工序单价表(工序报价编号 int,产品部件编号 int,工序编号 int,单价 int)
insert into 工序单价表 select 1,1,1001,100
insert into 工序单价表 select 2,1,1002,200
insert into 工序单价表 select 3,2,1001,1000
insert into 工序单价表 select 4,2,1002,2000



--利用动态SQL实现交叉表查询
declare @s varchar(8000),@v varchar(8000)
set @s = ''
set @v = ''

select
@s = @s + ','+部件名称+'=sum(case when 产品部件编号='+rtrim(编号)+' then 单价 end)',
@v = @v + ','+部件名称
from
产品部件表
where
产品款号 = 'AB'
order by
编号

set @s = 'select a.工序编号,a.工序名称'+@s+',合计=sum(单价) from 工序表 a,工序单价表 b where a.工序编号 = b.工序编号 group by a.工序编号,a.工序名称 with rollup'
set @v = 'select [产品款号(AB)] = isnull(a.工序名称,''合计'')'+@v+',合计 from('+@s+') a where a.工序编号 is null or a.工序名称 is not null order by case when a.工序编号 is null then 2 else 1 end,a.工序编号'

exec(@v)


--输出结果
产品款号(AB) 部件一 部件二 合计
------------ ------- ------ ------
工序一 100 1000 1100
工序二 200 2000 2200
合 计 300 3000 3300