27,579
社区成员
发帖
与我相关
我的任务
分享
(1 + row_number() over (partition by a.BOMHeaderID order by a.DistrictLength))/2
可以将,相同尺寸记录,按2条的方式进行排序,从而进行最终的合并处理。
非常感谢。
其实这个项目,还有非常多样化的情况。
比如有以下几种情况:
条件:当Area面积<=1.5 (m2)以内,计算以下打包规则:
1、长、宽相同时;
2、长相同,宽不同时, ABS[宽差值] / MAX[宽] < (2/3) --小于最大宽面的三分之二
3、宽相同,长不同时, ABS[长差值] / MAX[长 < (2/3) --小于最大长度的三分之二
4、图号(SingleSharpName)包含A的,不打包。
5、超1.5时,单独打包。
请教一下,象这种多样性情况的需求,考虑维护的方便性,如何实现把它写成函数会不会好些?
特别是上述4、5点可能会设定成开关方式。
再次感谢!!
CREATE TABLE #T
(
id INT,
headerid VARCHAR(10),
length INT,
width INT,
singleshap VARCHAR(10)
)
INSERT INTO #T VALUES
(1,16915,1159,554,'C1-F1'),
(2,16915,1159,554,'C1-F2'),
(3,16915,1159,554,'C1-F3'),
(4,16915,1159,554,'C1-F4')
INSERT INTO #T VALUES
(5,19616,1203,658,'C1-F1'),
(6,19616,1203,658,'C1-F2'),
(7,19616,1203,658,'C1-F3')
;
WITH ct
AS
(
SELECT
headerid,
CONVERT(varchar(10),length) AS length,
CONVERT(varchar(10),width) AS width,
(1+ROW_NUMBER() OVER(PARTITION BY headerid ORDER BY singleshap))/2 AS x
FROM #T
)
SELECT
STUFF( (SELECT '||'+width+'x'+ct.length FROM ct WHERE ct.headerid=A.headerid AND x=A.x
FOR XML PATH('')
) ,1,2,'') AS Caculatedsize, COUNT(1) AS SlicesNum, 1 AS BoxCount FROM CT A
GROUP BY A.headerid,x
ORDER BY headerid
SELECT STUFF((SELECT '||'+ltrim(b.DistrictWidth)+'x'+ltrim(b.DistrictLength) FROM ctea AS b
WHERE a.DistrictLength=b.DistrictLength AND a.flag=b.flag FOR XML PATH('')),1,2,'') AS CalculatedSize
,count(SingleShapeName) AS SlicesNum
,1 AS BoxCount
FROM ctea AS a WHERE a.flag not in (1,2,3)
GROUP BY a.DistrictLength,a.flag
结果就成了,4片玻璃,打包在1个木箱中。
上面SQL中flag标识,只针对尺寸相同的玻璃,做了一个相同的标记。语句是这样的:
最终错误的合并成: