34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] nvarchar(22),[status] nvarchar(21))
Insert #A
select N'03',N'Y' union all
select N'04',N'N' union all
select N'05',N'Y'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([id] nvarchar(22),[TC01] nvarchar(26),[TC02] decimal(18,8),[TC03] int)
Insert #B
select N'03',N'AS-AS',7.00,0 union all
select N'03',N'AS-AS',7.00,null union all
select N'03',N'AS-AS',7.00,null union all
select N'05',N'AS-AS',7.00,null union all
select N'05',N'AS-AW',7.00,null union all
select N'05',N'AS-AQE',7.00,null
Go
--测试数据结束
SELECT id,
TC01,
TC02,
CASE
WHEN rn1 = rn2 THEN
t1.sumtc03
ELSE
TC03
END AS tc03
FROM
(
SELECT #B.*,
COUNT(TC01) OVER (PARTITION BY #B.id) AS rn1,
COUNT(TC01) OVER (PARTITION BY #B.id, TC01) AS rn2,
SUM(TC02) OVER (PARTITION BY #B.id ORDER BY #B.id) AS sumtc03
FROM #B
JOIN
(SELECT * FROM #A WHERE status = 'Y') t
ON t.id = #B.id
) t1;