27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([D60_RKEY] nvarchar(22),[PARTS_ORDERED] int,[BASE_WO] nvarchar(22),[QUAN_SCH] int)
Insert #T
select N'R1',10,N'B1',5 union all
select N'R1',10,N'B2',10 union all
select N'R2',20,N'B3',44 union all
select N'R3',15,N'B3',44 union all
select N'R4',30,N'B4',40 union all
select N'R4',30,N'B5',50 union all
select N'R5',40,N'B5',50
Go
--测试数据结束
SELECT t.BASE_WO ,
( SELECT SUM(PARTS_ORDERED)
FROM #T a
WHERE a.BASE_WO = t.BASE_WO
) PARTS_ORDERED ,
( SELECT SUM(QUAN_SCH)
FROM #T a
WHERE a.D60_RKEY = t1.D60_RKEY
) QUAN_SCH
FROM ( SELECT DISTINCT
BASE_WO
FROM #T
) t
JOIN ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY BASE_WO ORDER BY D60_RKEY ) AS rn
FROM #T
) t1 ON t1.BASE_WO = t.BASE_WO
AND rn = 1
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([D60_RKEY] nvarchar(22),[PARTS_ORDERED] int,[BASE_WO] nvarchar(22),[QUAN_SCH] int)
Insert #tab
select N'R1',10,N'B1',5 union all
select N'R1',10,N'B2',10 union all
select N'R2',20,N'B3',44 union all
select N'R3',15,N'B3',44 union all
select N'R4',30,N'B4',40 union all
select N'R4',30,N'B5',50 union all
select N'R5',40,N'B5',50
Go
SELECT DISTINCT base_wo,
SUM(parts_ordered) OVER(PARTITION BY base_wo) AS parts_ordered ,
SUM(quan_sch) OVER(PARTITION BY d60_rkey) AS d60_rkey
from #tab
base_wo parts_ordered d60_rkey
---------------------- ------------- -----------
B1 10 15
B2 10 15
B3 35 44
B4 30 90
B5 70 50
B5 70 90
按我的理解可以得到一个这样的结果,不知道行不行