表1：产品表， 表2：配件表 现在提供一个产品数量，要计算出所需的配件。比如：表1 产品表：
产品编号 产品名称 颜色 数量
001 台式电脑 红色 10
001 台式电脑 黑色 20
001 台式电脑 粉色 30表2 配件表：
产品编号 配件名称 规格 用量
001 显示器 19” 1台
001 键盘 套装 1套
001 机箱 空箱 1个
001 音箱 2.0 1套表1和表2通过产品编号字段关联，现在要将两个表中的数据计算得出每种颜色的电脑所需的配件是多少，比如得出下表的记录：
所需配件清单：
显示器 红色 19“ 10台
黑色 19” 20台
粉色 19“ 30台
键盘 红色 10套
黑色 20套
粉色 30套
相箱 红色 10个
黑色 20个
粉色 30个
键盘 红色 10套
黑色 20套
粉色 30套请教各位有什么好的解决思路？
产品编号 产品名称 颜色 数量
001 台式电脑 红色 10
001 台式电脑 黑色 20
001 台式电脑 粉色 30表2 配件表：
产品编号 配件名称 规格 用量
001 显示器 19” 1台
001 键盘 套装 1套
001 机箱 空箱 1个
001 音箱 2.0 1套表1和表2通过产品编号字段关联，现在要将两个表中的数据计算得出每种颜色的电脑所需的配件是多少，比如得出下表的记录：
所需配件清单：
显示器 红色 19“ 10台
黑色 19” 20台
粉色 19“ 30台
键盘 红色 10套
黑色 20套
粉色 30套
相箱 红色 10个
黑色 20个
粉色 30个
键盘 红色 10套
黑色 20套
粉色 30套请教各位有什么好的解决思路？
join 表2 on 表1.产品编号=表2.产品编号
where .....
没有合并同类项
join 表2 on 表1.产品编号=表2.产品编号
where .....
insert into tb1 values('001' , '台式电脑' , '红色' , 10 )
insert into tb1 values('001' , '台式电脑' , '黑色' , 20 )
insert into tb1 values('001' , '台式电脑' , '粉色' , 30 )
create table tb2(产品编号 varchar(10) , 配件名称 varchar(10) , 规格 varchar(10) , 用量 int, 单位 varchar(10))
insert into tb2 values('001' , '显示器' , '19”' , 1,'台')
insert into tb2 values('001' , '键盘' , '套装' , 1,'套')
insert into tb2 values('001' , '机箱' , '空箱' , 1,'个')
insert into tb2 values('001' , '音箱' , '2.0 ' , 1,'套')
goselect a.配件名称 , b.颜色 , b.数量*a.用量 as 总数量 from tb2 a cross join tb1 b where a.产品编号 = b.产品编号 order by a.配件名称 , b.颜色drop table tb1,tb2/*
配件名称 颜色 总数量
---------- ---------- -----------
机箱 粉色 30
机箱 黑色 20
机箱 红色 10
键盘 粉色 30
键盘 黑色 20
键盘 红色 10
显示器 粉色 30
显示器 黑色 20
显示器 红色 10
音箱 粉色 30
音箱 黑色 20
音箱 红色 10（所影响的行数为 12 行）
*/
create table tb1(产品编号 varchar(10) , 产品名称 varchar(10) , 颜色 varchar(10) , 数量 int)
insert into tb1 values('001' , '台式电脑' , '红色' , 10 )
insert into tb1 values('001' , '台式电脑' , '黑色' , 20 )
insert into tb1 values('001' , '台式电脑' , '粉色' , 30 )
create table tb2(产品编号 varchar(10) , 配件名称 varchar(10) , 规格 varchar(10) , 用量 varchar(10))
insert into tb2 values('001' , '显示器' , '19”' , '1台')
insert into tb2 values('001' , '键盘' , '套装' , '1套')
insert into tb2 values('001' , '机箱' , '空箱' , '1个')
insert into tb2 values('001' , '音箱' , '2.0 ' , '1套')
goselect a.配件名称 , b.颜色 , b.数量*cast(left(a.用量,len(a.用量)-1) as int) as 总数量 from tb2 a cross join tb1 b where a.产品编号 = b.产品编号 order by a.配件名称 , b.颜色drop table tb1,tb2/*
配件名称 颜色 总数量
---------- ---------- -----------
机箱 粉色 30
机箱 黑色 20
机箱 红色 10
键盘 粉色 30
键盘 黑色 20
键盘 红色 10
显示器 粉色 30
显示器 黑色 20
显示器 红色 10
音箱 粉色 30
音箱 黑色 20
音箱 红色 10（所影响的行数为 12 行）
*/
insert into tb1 values('001' , '台式电脑' , '红色' , 10 )
insert into tb1 values('001' , '台式电脑' , '黑色' , 20 )
insert into tb1 values('001' , '台式电脑' , '粉色' , 30 )
create table tb2(产品编号 varchar(10) , 配件名称 varchar(10) , 规格 varchar(10) , 用量 varchar(10))
insert into tb2 values('001' , '显示器' , '19”' , '1台')
insert into tb2 values('001' , '键盘' , '套装' , '1套')
insert into tb2 values('001' , '机箱' , '空箱' , '1个')
insert into tb2 values('001' , '音箱' , '2.0 ' , '1套')
goselect a.配件名称 , b.颜色 , cast(b.数量*cast(left(a.用量,len(a.用量)-1) as int) as varchar) + right(a.用量,1) as 总数量 from tb2 a cross join tb1 b where a.产品编号 = b.产品编号 order by a.配件名称 , b.颜色drop table tb1,tb2/*
配件名称 颜色 总数量
---------- ---------- -----------
配件名称 颜色 总数量
---------- ---------- --------------------------------
机箱 粉色 30个
机箱 黑色 20个
机箱 红色 10个
键盘 粉色 30套
键盘 黑色 20套
键盘 红色 10套
显示器 粉色 30台
显示器 黑色 20台
显示器 红色 10台
音箱 粉色 30套
音箱 黑色 20套
音箱 红色 10套（所影响的行数为 12 行）
*/
DECLARE @a TABLE (产品编号 varchar(10) , 产品名称 varchar(10) , 颜色 varchar(10) , 数量 int)
insert into @a values('001' , '台式电脑' , '红色' , 10 )
insert into @a values('001' , '台式电脑' , '黑色' , 20 )
insert into @a values('001' , '台式电脑' , '粉色' , 30 )
DECLARE @b TABLE(产品编号 varchar(10) , 配件名称 varchar(10) , 规格 varchar(10) , 用量 int, 单位 varchar(10))
insert into @b values('001' , '显示器' , '19”' , 1,'台')
insert into @b values('001' , '键盘' , '套装' , 1,'套')
insert into @b values('001' , '机箱' , '空箱' , 1,'个')
insert into @b values('001' , '音箱' , '2.0 ' , 1,'套')select 配件名称=case when 颜色=(select top 1 颜色 from @a) then 配件名称 else '' end,
颜色,
规格,
ltrim(数量)+单位 数量
from @a a cross join @b b
--result
/*
配件名称 颜色 规格 数量
---------- ---------- ---------- ----------------------
显示器 红色 19” 10台
黑色 19” 20台
粉色 19” 30台
键盘 红色 套装 10套
黑色 套装 20套
粉色 套装 30套
机箱 红色 空箱 10个
黑色 空箱 20个
粉色 空箱 30个
音箱 红色 2.0 10套
黑色 2.0 20套
粉色 2.0 30套*/
insert into tb1 values('001' , '台式电脑' , '红色' , 10 )
insert into tb1 values('001' , '台式电脑' , '黑色' , 20 )
insert into tb1 values('001' , '台式电脑' , '粉色' , 30 )
create table tb2(产品编号 varchar(10) , 配件名称 varchar(10) , 规格 varchar(10) , 用量 varchar(10))
insert into tb2 values('001' , '显示器' , '19”' , '1台')
insert into tb2 values('001' , '键盘' , '套装' , '1套')
insert into tb2 values('001' , '机箱' , '空箱' , '1个')
insert into tb2 values('001' , '音箱' , '2.0 ' , '1套')
goselect a.配件名称 , b.颜色 , cast(b.数量*cast(left(a.用量,len(a.用量)-1) as int) as varchar) + right(a.用量,1) as 总数量 from tb2 a cross join tb1 b where a.产品编号 = b.产品编号 order by a.配件名称 , b.颜色drop table tb1,tb2/*
配件名称 颜色 总数量
---------- ---------- -----------
配件名称 颜色 总数量
---------- ---------- --------------------------------
机箱 粉色 30个
机箱 黑色 20个
机箱 红色 10个
键盘 粉色 30套
键盘 黑色 20套
键盘 红色 10套
显示器 粉色 30台
显示器 黑色 20台
显示器 红色 10台
音箱 粉色 30套
音箱 黑色 20套
音箱 红色 10套（所影响的行数为 12 行）
*/
create table tb1(产品编号 varchar(10) , 产品名称 varchar(10) , 颜色 varchar(10) , 数量 int)
insert into tb1 values('001' , '台式电脑' , '红色' , 10 )
insert into tb1 values('001' , '台式电脑' , '黑色' , 20 )
insert into tb1 values('001' , '台式电脑' , '粉色' , 30 )create table tb2(产品编号 varchar(10) , 配件名称 varchar(10) , 规格 varchar(10) , 用量 varchar(10))insert into tb2 values('001' , '显示器' , '19' , '1台')
insert into tb2 values('001' , '键盘' , '套装' , '1套')
insert into tb2 values('001' , '机箱' , '空箱' , '1个')
insert into tb2 values('001' , '音箱' , '2.0 ' , '1套')
select
case when tt.颜色=(select top 1 颜色 from tb1) then t.配件名称 else '' end 配件名称,tt.颜色 , Convert(varchar(20),convert(int,left(t.用量,1))*tt.数量)+right(t.用量,1) 数量 ,t.规格
from tb2 t inner join tb1 tt on t.产品编号=tt.产品编号 order by case when 规格='19' then 1 when 规格='套装' then '2' when 规格='2.0 ' then 3 else 4 end ,数量
'
显示器 红色 10台 19
黑色 20台 19
粉色 30台 19
键盘 红色 10套 套装
黑色 20套 套装
粉色 30套 套装
音箱 红色 10套 2.0
黑色 20套 2.0
粉色 30套 2.0
机箱 红色 10个 空箱
黑色 20个 空箱
粉色 30个 空箱'
insert into tb1 values('001' , '台式电脑' , '红色' , 10 )
insert into tb1 values('001' , '台式电脑' , '黑色' , 20 )
insert into tb1 values('001' , '台式电脑' , '粉色' , 30 )
create table tb2(产品编号 varchar(10) , 配件名称 varchar(10) , 规格 varchar(10) , 用量 varchar(10))
insert into tb2 values('001' , '显示器' , '19”' , '1台')
insert into tb2 values('001' , '键盘' , '套装' , '1套')
insert into tb2 values('001' , '机箱' , '空箱' , '1个')
insert into tb2 values('001' , '音箱' , '2.0 ' , '1套')
go
select a.配件名称 , b.颜色 , cast(b.数量*cast(left(a.用量,len(a.用量)-1) as int) as varchar) + right(a.用量,1) as 总数量 from tb2 a cross join tb1 b where a.产品编号 = b.产品编号 order by a.配件名称 , b.颜色
表1---订单表：
产品编号 产品名称 颜色 规格 数量
001 台式电脑 红色 保修1年 3
001 台式电脑 红色 保修2年 3
001 台式电脑 红色 保修3年 4
001 台式电脑 黑色 保修1年 10
001 台式电脑 黑色 保修2年 5
001 台式电脑 黑色 保修3年 5 001 台式电脑 粉色 保修1年 5
001 台式电脑 粉色 保修1年 5
001 台式电脑 粉色 保修1年 5002 台式电脑 粉色 保修1年 30
002 台式电脑 粉色 保修2年 30
002 台式电脑 粉色 保修3年 30
表2---配件清单表：
产品编号 配件编号 配件名称 规格 用量 单位 配比方法
001 LG01 显示器 19” 1 台 按颜色配
001 SA02 键盘 套装 1 套 按颜色配
001 JT08 机箱 空箱 1 个 按颜色配
001 ZX01 纸箱 7层 3 个 按数量配
001 DY01 插座 带保险 1 个 按数量配说明：表2配件清单表中，将单位单独存一个字段，配加了配比方法字段，这个字段有两个值，一个是按颜色配，一个是按数量，比如买一台黑色的电脑，那么为了颜色的统一，显示器，机箱，键盘，音箱等设备都要配成黑色的。按数量配：表示这个配件不按颜色区分，比如买电脑会送一个电源查座，不管是黑色电脑还是红色电脑都是送同一种颜色的插座。表1订单表中因为同一种颜色的电脑保修的年份不同，所以在下单时要标识出来，但在进行运算之前是不是要先将数据汇总一下呢？ 还是直接在运算代码中汇总？如果按之前发的示例数据要先汇总成以下结果：产品编号 产品名称 颜色 数量
001 台式电脑 红色 10
001 台式电脑 黑色 20
001 台式电脑 粉色 15002 台式电脑 粉色 90计算后的结果：产品编号 配件编号 配件名称 规格 用量 单位 颜色 需求量
001 LG01 显示器 19” 1 台 红色 10
001 LG01 显示器 19” 1 台 黑色 20
001 LG01 显示器 19” 1 台 粉色 15
001 SA02 键盘 套装 1 套 红色 10
001 SA02 键盘 套装 1 套 黑色 20
001 SA02 键盘 套装 1 套 粉色 15
001 JT08 机箱 空箱 1 个 红色 10
001 JT08 机箱 空箱 1 个 黑色 20
001 JT08 机箱 空箱 1 个 粉色 15
001 ZX01 纸箱 7层 3 个 135
001 DY01 插座 带保险 1 个 45 请问以上计算结果，是不是要用到临时表来解决,计算方法是不是会很复杂？
create table tb3(产品编号 varchar(10) , 产品名称 varchar(10) , 颜色 varchar(10) ,规格 varchar(10), 数量 int)
insert into tb3 values('001' , '台式电脑' , '红色' ,'保修1年', 3 )
insert into tb3 values('001' , '台式电脑' , '红色' ,'保修2年', 3 )
insert into tb3 values('001' , '台式电脑' , '红色' ,'保修3年', 4 )
insert into tb3 values('001' , '台式电脑' , '黑色' ,'保修1年', 10 )
insert into tb3 values('001' , '台式电脑' , '黑色' ,'保修2年', 5 )
insert into tb3 values('001' , '台式电脑' , '黑色' ,'保修3年', 5 )
insert into tb3 values('001' , '台式电脑' , '粉色' ,'保修1年', 5 )
insert into tb3 values('001' , '台式电脑' , '粉色' ,'保修2年', 5 )
insert into tb3 values('001' , '台式电脑' , '粉色' ,'保修3年', 5 )
insert into tb3 values('002' , '台式电脑' , '粉色' ,'保修1年', 30 )
insert into tb3 values('002' , '台式电脑' , '粉色' ,'保修2年', 30 )
insert into tb3 values('002' , '台式电脑' , '粉色' ,'保修3年', 30 )create table tbBOM(产品编号 varchar(10) ,配件编号 varchar(10), 配件名称 varchar(10) ,规格 varchar(10) ,用量 int,单位 varchar(10),配比方法 varchar(10))
insert into tbBOM values('001', 'LG01','显示器',' 19”', 1,'台','按颜色配 ')
insert into tbBOM values('001', 'SA02', '键盘', '套装', 1, '套', '按颜色配')
insert into tbBOM values('001', 'JT08', '机箱', '空箱', 1, '个', '按颜色配')
insert into tbBOM values('001', 'ZX01', '纸箱', '7层' , 3, '个', '按数量配')
insert into tbBOM values('001', 'DY01', '插座', '带保险', 1, '个', '按数量配')create view V_tb3
as
select 产品编号,产品名称,颜色,sum(数量) as 数量 from tb3 group by 产品编号,产品名称,颜色
from
表2 as a
inner join
表1 as b
on a.产品编号 = b.产品编号
order by a.配件名称,b.颜色
from v_tb3 a cross join tbbom b where a.产品编号=b.产品编号 and b.配比方法='按颜色配'
order by b.配件编号,a.颜色,b.配件名称,b.规格,b.用量,b.单位配件编号 颜色 配件名称 规格 用量 单位 需求量
---------- ---------- ---------- ---------- ----------- ---------- -----------
JT08 粉色 机箱 空箱 1 个 15
JT08 黑色 机箱 空箱 1 个 20
JT08 红色 机箱 空箱 1 个 10
LG01 粉色 显示器 19” 1 台 15
LG01 黑色 显示器 19” 1 台 20
LG01 红色 显示器 19” 1 台 10
SA02 粉色 键盘 套装 1 套 15
SA02 黑色 键盘 套装 1 套 20
SA02 红色 键盘 套装 1 套 10（所影响的行数为 9 行）现在遇到的问题是：
1、这个视图是不是有必要？如果不建这个视图，应该怎么解决？
2、现在只能查出一部分的数据，还有两个配件纸箱和插座没有办法查出来，因为它们是按总数量来计算，请问下面这些数据应该怎样算出来。
配件编号 颜色 配件名称 规格 用量 单位 需求量
ZX01 纸箱 7层 3 个 135
DY01 插座 带保险 1 个 45 麻烦大家帮忙看看，谢谢!