成套件问题

Question

基本信息表（注意里面床Bed由三个部件组成即Bed-1,Bed-2,Bed-3）编号 名称 单位 父项编号
Bed 床 套 0
Bed-1 床高片 件 Bed
Bed-2 床低片 件 Bed
Bed-3 床挺 件 Bed
Dresser 梳妆台 件 0
Mirror 镜子 件 0
-------------------------------------------------------------
发票（由件组成）编号 数量
Bed-1 3
Bed-2 4
Bed-3 5
Dresser 7
Mirror 7
-------------------------------------------------
我希望查询出结果是把发票中能组成套的先组成套显示即编号 数量
Bed 3
Bed-2 1
Bed-3 2
Dresser 7
Mirror 7
--------------------------------------------------
这个查询SQL怎么写？不知道各位看懂了没，就是查出来是3套床，另外多出1个Bed-2，2个Bed-3。然后Dresser 和 Mirror 反正一个一套，也就是和原来一样。thanks for your suggestion.

Answer 1

to Marco08:你的想法确实仔细，按道理后面应该再加个组成套需要的数量。不过那样太复杂了。目前我们就假设床只有1个高片1个低片1个床挺组成。没有你说的那种情况。To Xiaoku:不是太明白你讲的意思？如果我没理解错的话，实际上这个关系就是多层的了，父项就是上层吧

Answer 2

我的意思是:
Bed-1 床高片 件 Bed
Bed-2 床低片 件 Bed
Bed-3 床挺 件 Bed这些下面还有下一层马?

Answer 3

--环境
create table inf
(
[编号] varchar(20),
[名称] varchar(20),
[单位] varchar(10),
[父项编号] varchar(20)
)insert into inf select 'Bed',    '床',    '套',  '0'
insert into inf select 'Bed-1',  '床高片', '件',   'Bed'
insert into inf select 'Bed-2',  '床低片', '件',   'Bed'
insert into inf select 'Bed-3',  '床挺',    '件',  'Bed'
insert into inf select 'Dresser','梳妆台', '件',   '0'
insert into inf select 'Mirror', '镜子',   '件',   '0'create table inv
(
[编号] varchar(20),
[数量] int
)insert into inv select 'Bed-1',  3
insert into inv select 'Bed-2',  4
insert into inv select 'Bed-3',  5
insert into inv select 'Dresser',7
insert into inv select 'Mirror', 7--语句
select [父项编号] as [编号],min([数量]) as [数量] from 
(select a.[编号],a.[数量],b.[父项编号]
 from inv a inner join inf b on a.[编号] = b.[编号])c
where [父项编号]<> '0' 
       and not exists(select 1 from inf where [父项编号] = c.[父项编号] and [编号] not in (select [编号] from inv))
group by [父项编号]
union 
select c.[编号],c.[数量] - isnull(d.[数量],0) as [数量] from 
(select a.[编号],a.[数量],b.[父项编号]
 from inv a inner join inf b on a.[编号] = b.[编号])c
left join 
(select [父项编号],min([数量]) as [数量] from 
(select a.[编号],a.[数量],b.[父项编号]
 from inv a inner join inf b on a.[编号] = b.[编号])c
where [父项编号]<> '0' 
       and not exists(select 1 from inf where [父项编号] = c.[父项编号] and [编号] not in (select [编号] from inv))
group by [父项编号]
)d on c.[父项编号] = d.[父项编号]
where c.[数量] - isnull(d.[数量],0) > 0--结果
编号                   数量          
-------------------- ----------- 
Bed                  3
Bed-2                1
Bed-3                2
Dresser              7
Mirror               7（所影响的行数为 5 行）感觉思路不是很好
期待高手！！！

Answer 4

复杂，竟然是一条SQL语句解决的。呵呵。
还有没有高手啊？也许存储过程啊什么的也可以But anyway,谢谢coolingpipe

Answer 5

--分成两步代码看起来会好一些
--插入临时表
select a.[编号],a.[数量],b.[父项编号]
into #inv
from inv a inner join inf b on a.[编号] = b.[编号]--语句
select [父项编号] as [编号],min([数量]) as [数量] from 
#inv c
where [父项编号]<> '0' 
       and not exists(select 1 from inf where [父项编号] = c.[父项编号] and [编号] not in (select [编号] from inv))
group by [父项编号]
union 
select c.[编号],c.[数量] - isnull(d.[数量],0) as [数量] from 
#inv c
left join 
(select [父项编号],min([数量]) as [数量] from 
 #inv c
 where [父项编号]<> '0' 
       and not exists(select 1 from inf where [父项编号] = c.[父项编号] and [编号] not in (select [编号] from inv))
group by [父项编号]
)d on c.[父项编号] = d.[父项编号]
where c.[数量] - isnull(d.[数量],0) > 0--删除环境
drop #inv
drop inv
drop inf

Answer 6

我觉得你这个问题是业务处理设计的问题，一般情况下业务上应该做打包处理
首先应该建立一张打包商品的维护表，类似这样ID 父商品ID 子商品ID 子商品数量提前维护好这张打包商品维护表，业务上先打包，等于先做一次相关子商品的出库和父商品
的入库，然后正常做出库就可以了；这样从做法上也规范些，账目不容易产生混乱，而且拓
展性也好些。

Answer 7

--再或者可以这样
select [父项编号] as [编号],min([数量]) as [数量] 
into #inv
from 
(select a.[编号],a.[数量],b.[父项编号]
 from inv a inner join inf b on a.[编号] = b.[编号])c
where [父项编号]<> '0' 
       and not exists(select 1 from inf where [父项编号] = c.[父项编号] and [编号] not in (select [编号] from inv))
group by [父项编号]
insert into #inv
select c.[编号],c.[数量] - isnull(d.[数量],0) as [数量] from 
(select a.[编号],a.[数量],b.[父项编号]
 from inv a inner join inf b on a.[编号] = b.[编号])c
left join 
#inv d on c.[父项编号] = d.[编号]
where c.[数量] - isnull(d.[数量],0) > 0select * from #inv order by [编号]
--结果
编号                   数量          
-------------------- ----------- 
Bed                  3
Bed-2                1
Bed-3                2
Dresser              7
Mirror               7（所影响的行数为 5 行）

Answer 8

思路跟你的差不多...都是先找出有子项的东西--这个有点麻烦
create table 基本信息表
(
编号 varchar(20),
名称 varchar(20),
单位 varchar(20),
父项编号 varchar(20)
)create table 发票
(
编号 varchar(20),
数量 int
)insert into 基本信息表 select 'Bed' ,'床' ,'套', '0'
union all select 'Bed-1', '床高片', '件', 'Bed'
union all select 'Bed-2', '床低片', '件', 'Bed'
union all select 'Bed-3', '床挺', '件', 'Bed'
union all select 'Dresser', '梳妆台', '件', '0'
union all select 'Mirror', '镜子', '件', '0'insert into 发票 select 'Bed-1', 3
union all select 'Bed-2', 4
union all select 'Bed-3', 5
union all select 'Dresser', 7
union all select 'Mirror', 7select case when x.数量-y.数量= 0 then x.父编号 else x.编号 end as 编号
,case when x.数量-y.数量= 0 then x.数量 else x.数量-y.数量 end as 数量
from 
(
select a.编号 as 父编号,b.编号,c.数量 from 基本信息表 a
join 基本信息表 b on a.编号 =b.父项编号
join 发票 c on b.编号=c.编号
) x
join 
(
select a.编号 as 父编号,min(c.数量) as 数量 from 基本信息表 a
join 基本信息表 b on a.编号 =b.父项编号
join 发票 c on b.编号=c.编号
group by a.编号
) y on x.父编号 =y.父编号 
union all 
select t.编号 ,m.数量
from 
(
select 编号 from 基本信息表 a 
where 父项编号='0' 
and not exists (select 1 from 基本信息表 where 父项编号 =a.编号)
) t join 发票 m on t.编号=m.编号编号                   数量          
-------------------- ----------- 
Bed                  3
Bed-2                1
Bed-3                2
Dresser              7
Mirror               7（所影响的行数为 5 行）

Answer 9

xiaoku的思路有点问题哈
你可以测试一下，如果发票表中没有
'Bed-3', 5
这一条的话，问题就出来了！
：）