看错了 试试这个 --测试数据 if not object_id(N'Tempdb..#T1') is null drop table #T1 Go Create table #T1([序号] int,[名称] nvarchar(22),[数量] int) Insert #T1 select 1,N'AA',12 union all select 2,N'AA',13 union ALL select 3,N'BB',14 union all select 4,N'BB',13 union all select 5,N'CC',15 GO if not object_id(N'Tempdb..#T2') is null drop table #T2 Go Create table #T2([名称] nvarchar(22),[总数] int) Insert #T2 select N'AA',20 union all select N'BB',30 union all select N'CC',15 Go --测试数据结束 ;WITH ctea AS ( SELECT *,ROW_NUMBER()OVER(PARTITION BY 名称 ORDER BY 序号) AS rn FROM #T1 ),cteb AS ( SELECT ctea.* , 数量 AS 余数 , 总数 FROM ctea JOIN #T2 ON #T2.名称 = ctea.名称 WHERE rn = 1 UNION ALL SELECT ctea.* , cteb.总数 - cteb.数量 , cteb.总数 - cteb.数量 FROM ctea JOIN cteb ON cteb.rn + 1 = ctea.rn AND cteb.名称 = ctea.名称 JOIN #T2 ON #T2.名称 = ctea.名称 ) SELECT 序号,名称,数量,余数 FROM cteb ORDER BY 序号
e.g.;WITH CTET1 AS (SELECT * ,(SELECT SUM(数量) FROM T1 WHERE 名称=a.名称 AND 序号<=a.序号) AS Sum数量 FROM T1 AS a) SELECT a.序号 , a.名称 , a.数量 , CASE WHEN b.总数>=a.Sum数量 THEN a.数量 ELSE b.总数-a.Sum数量+a.数量 END AS 余数 FROM CTET1 AS a INNER JOIN T2 AS b ON a.名称=b.名称;
SQL2012 之后的版本Sum数量可用 SUM(数量)OVER(PARTITION BY 名称 ORDER BY 序号) AS Sum数量
with cte as (SELECT * FROM TABLE_A A OUTER APPLY (SELECT ISNULL(SUM([数量]),0) AS SUB_TOTAL FROM TABLE_A WHERE [名称]=A.[名称] AND [序号]<A.[序号]) AS B)
SELECT *, CASE WHEN SUB_TOTAL=0 THEN [数量] ELSE [总数]-SUB_TOTAL END AS 余数 FROM CTE A LEFT JOIN TABLE_B B ON A.名称=B.名称
试试这个
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([序号] int,[名称] nvarchar(22),[数量] int)
Insert #T1
select 1,N'AA',12 union all
select 2,N'AA',13 union ALL
select 3,N'BB',14 union all
select 4,N'BB',13 union all
select 5,N'CC',15
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([名称] nvarchar(22),[总数] int)
Insert #T2
select N'AA',20 union all
select N'BB',30 union all
select N'CC',15
Go
--测试数据结束
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 名称 ORDER BY 序号) AS rn FROM #T1
),cteb AS (
SELECT ctea.* ,
数量 AS 余数 ,
总数
FROM ctea
JOIN #T2 ON #T2.名称 = ctea.名称
WHERE rn = 1
UNION ALL
SELECT ctea.* ,
cteb.总数 - cteb.数量 ,
cteb.总数 - cteb.数量
FROM ctea
JOIN cteb ON cteb.rn + 1 = ctea.rn
AND cteb.名称 = ctea.名称
JOIN #T2 ON #T2.名称 = ctea.名称
)
SELECT 序号,名称,数量,余数 FROM cteb ORDER BY 序号
AS
(SELECT
*
,(SELECT SUM(数量) FROM T1 WHERE 名称=a.名称 AND 序号<=a.序号) AS Sum数量
FROM T1 AS a)
SELECT a.序号
, a.名称
, a.数量
, CASE WHEN b.总数>=a.Sum数量 THEN a.数量
ELSE b.总数-a.Sum数量+a.数量
END AS 余数
FROM CTET1 AS a
INNER JOIN T2 AS b ON a.名称=b.名称;
with cte
as
(SELECT * FROM TABLE_A A
OUTER APPLY (SELECT ISNULL(SUM([数量]),0) AS SUB_TOTAL FROM TABLE_A WHERE [名称]=A.[名称] AND [序号]<A.[序号]) AS B)
SELECT *,
CASE WHEN SUB_TOTAL=0 THEN [数量] ELSE [总数]-SUB_TOTAL END AS 余数
FROM CTE A
LEFT JOIN TABLE_B B ON A.名称=B.名称