27,579
社区成员
发帖
与我相关
我的任务
分享
if not object_id(N'Tempdb..#T') is null
drop table #T
go
create table #T (BOMHeaderID int,Length decimal(10,2),Width decimal(10,2),SndQty decimal(10,2))
insert #T
select 5454,806,385,1.00 union ALL
select 5454,806,385,1.00 union ALL
select 5454,806,385,1.00 union ALL
select 5454,806,385,1.00 union ALL
select 5454,632,1272,1.00 union ALL
select 5454,806,1446,1.00 union ALL
select 6918,605,1322,1.00 union ALL
select 6918,605,1322,1.00 union ALL
select 6918,688,388,1.00 union ALL
select 6918,688,388,1.00 union ALL
select 6918,688,588,1.00 union ALL
select 6918,688,588,1.00 union ALL
select 6918,1055,1420,1.00
go
select *
,(row_number() over (PARTITION BY BOMHeaderID order by Length)+1)/2 AS rn
,rtrim(Width) +'x'+ rtrim(Length) AS Size
,(Length * Width )/1000000 as Area
from #T
数据源表图:
优先级条件处理是:(从上往下优先)
1、当面积Area 大于1.5 时,直接优先单独打包。也就是BoxCount =1
2、当长、宽尺寸相同时,2条块玻璃打成1个,也就是BoxCount =1--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Length] int,[Width] INT,[Qty] decimal(18,2))
Insert #T
select 427,'1122',1.00 union ALL
select 427,'1122',1.00 union ALL
select 427,'1122',1.00 union ALL
select 427,'1122',1.00 union all
select 552,'536',1.00 union all
select 1187,'536',1.00 union all
select 552,'536',1.00
Go
--测试数据结束
;WITH cte AS (
Select *,(ROW_NUMBER()OVER(ORDER BY Length)+1)/2 AS rn ,RTRIM(Length)+'*'+RTRIM(Width) AS Size FROM #T
)
SELECT rn ,
STUFF(( SELECT '||' + cte.Size
FROM cte
WHERE rn = a.rn
FOR
XML PATH('')
), 1, 2, '') AS Size,
SUM(a.Qty) AS Num,
1 AS Count
FROM cte a
GROUP BY a.rn