关键ＳＱＬ ，求支招

[雷肿么了]

------- 问题描述 -----
--如果订单重量,超过订单服务(Ttype 字段)的所对应的服务(#Type 表)标识的重量,就需要拆单处理，反之不需要拆单处理.
--比如:
--订单 :Ref001 
--服务 : CH,  标识重量:2000(g)
--订单总重 : 10*100+15*100   = 2500(g)  > 2000(g)  此单就需要拆成每单总重不超过2000(g)的订单.

--所以拆完之后(新订单)　运行查看（拆成的新单要知道是从哪个订单拆过来的，所以有个ParentID　记录）,
拆成新的订单编号= 原始订单编号_拆成总单数_数字(从1开始)
select 'Ref001_2_1' as RefNum,1 as ParentID,'CH' as Ttype
union all
select 'Ref001_2_2' as RefNum,1 as ParentID,'CH' as Ttype

select 'Ref001_2_1' as RefNum ,'EAY100' as SKU,10 as Num,100 as UnitWeight
union all
select 'Ref001_2_1' as RefNum ,'EAY200' as SKU,10 as Num,100 as UnitWeight
union all
select 'Ref001_2_2' as RefNum ,'EAY200' as SKU,5 as Num,100 as UnitWeight



----- SQL ----

------ 服务表 ----
Create table #Type
(
  ID int identity(1,1),
  Code varchar(10), -- 服务类型, 订单  
  Weigth decimal    -- 单位(克),超过此重量需拆单 
)

insert into #Type select 'CH',2000
insert into #Type select 'BH',1000


--- 订单 ----
Create table #Order
(
  ID int identity(1,1),
  RefNum varchar(20),  --订单编号
  ParentID int,        --父ID, 用于拆单时记录
  Ttype varchar(10)    --服务类型 #Type
)

insert into #Order select  'Ref001','CH'
insert into #Order select  'Ref002','CH'
insert into #Order select  'Ref003','BH'

---- 商品 ----
create table #Product
(
  ID int identity(1,1),
  RefNum varchar(20),  --订单编号
  SKU varchar(10),
  Num int,
  UnitWeight decimal   -- 商品单重（克）
)

insert into #Product select 'Ref001','EAY100',10,100
insert into #Product select 'Ref001','EAY200',15,100
insert into #Product select 'Ref002','EAY300',30,80
insert into #Product select 'Ref003','EAY400',10,80


select * from #Type
select * from #Order
select * from #Product

drop table #Type
drop table #Order
drop table #Product

[雷肿么了]

自己顶下, 貌似要沉了.....

[Shawn]

--#1.查询出需要拆单的RefNum.
SELECT * FROM
(
	SELECT b.id, b.RefNum, a.Weigth, total=SUM(c.Num*c.UnitWeight)
	FROM #Type a
	INNER JOIN #Order b
		ON a.Code = b.TType
	INNER JOIN #Product c
		ON b.RefNum = c.RefNum
	GROUP BY b.id, b.RefNum, a.Weigth
) t
WHERE total > Weigth

--#2.循环#1中的RefNum,自己写拆单算法,拆单即可.