27,579
社区成员
发帖
与我相关
我的任务
分享
我明白了。2/2 = 1 ,3/2 =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 699,'2182',1.00 union all
select 699,'2182',1.00 union all
select 699,'2182',1.00 union all
select 699,'2182',1.00
Go
--测试数据结束
SELECT
(ROW_NUMBER()OVER(ORDER BY GETDATE())+1)/2 AS rn ,
ROW_NUMBER()OVER(ORDER BY GETDATE()) rn1
FROM #T
--测试数据
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 699,'2182',1.00 union all
select 699,'2182',1.00 union all
select 699,'2182',1.00 union all
select 699,'2182',1.00
Go
--测试数据结束
;WITH cte AS (
Select *,(ROW_NUMBER()OVER(ORDER BY GETDATE())+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,
SUM(a.Qty)/2 AS Count
FROM cte a
GROUP BY a.rn