A表
F1 F2 F3A1 B1 6
A2 B2 4
A3 B3 5B表
F1 F2 F3 F4 A1 B1 2 0
A2 B8 4 0
A1 B1 3 0
---------------------------------------
现希望达到以下效果:
F1 F2 F3 F4 A1 B1 2 2
A2 B8 4 0
A1 B1 3 3也就是说,在 a.f1=b.f1 and a.f2=b.f2 情况下,A表的F3分配给B表的F4,从下向下依次分配 一条SQL能搞定吗?
F1 F2 F3A1 B1 6
A2 B2 4
A3 B3 5B表
F1 F2 F3 F4 A1 B1 2 0
A2 B8 4 0
A1 B1 3 0
---------------------------------------
现希望达到以下效果:
F1 F2 F3 F4 A1 B1 2 2
A2 B8 4 0
A1 B1 3 3也就是说,在 a.f1=b.f1 and a.f2=b.f2 情况下,A表的F3分配给B表的F4,从下向下依次分配 一条SQL能搞定吗?
但是从测试数据看,b表left a表能满足条件
select b.f1,b.f2,b.f3, case when a.f1 is null then 0 else b.f3 end 'f4'
from B left join A on a.f1=b.f1 and a.f2=b.f2
SELECT *,ROW_NUMBER()OVER(PARTITION BY F1,F2 ORDER BY GETDATE())RN
FROM B
)
,CTE AS(
SELECT T1.F1,T1.F2,T1.F3
,CASE WHEN T2.F3>T1.F3 THEN T1.F3 ELSE T2.F3 END F4
,T2.F3-T1.F3 Balance
,T1.RN
FROM RNB T1
JOIN A T2 ON T1.F1=T2.F1 AND T1.F2=T2.F2
WHERE T1.RN=1
UNION ALL
SELECT T2.F1,T2.F2,T2.F3
,CASE WHEN T1.Balance>T2.F3 THEN T2.F3 ELSE T1.Balance END
,T1.Balance-T2.F3
,T2.RN
FROM CTE T1
JOIN RNB T2 ON T1.F1=T2.F1 AND T1.F2=T2.F2 AND T1.Balance>0 AND T1.RN+1=T2.RN
)
SELECT F1,F2,F3,F4 FROM CTE
UNION ALL
SELECT T1.F1,T1.F2,T1.F3,0
FROM B T1
LEFT JOIN CTE T2 ON T1.F1=T2.F1 AND T1.F2=T2.F2
WHERE T2.F1 IS NULL你的列名表名这么泛化,你能改得过来吗
select b.f1,b.f2,b.f3,f4=case when ' a.f1=b.f1 and a.f2=b.f2' then 'b.f3' else ' b.f4' END from a,b
没进行实际测试
INSERT INTO @t_TBA VALUES
('A1','B1','6'),
('A2','B2','4'),
('A3','B3','5');DECLARE @t_TBB TABLE ([F1] NVARCHAR(5),[F2] NVARCHAR(5),[F3] INT,[F4] INT);
INSERT INTO @t_TBB VALUES
('A1','B1','2','0'),
('A2','B8','4','0'),
('A1','B1','3','0'),
('A1','B1','3','0'); -- New Added...;WITH TBB_cte
AS
(
SELECT
[F1]
,[F2]
,[F3]
,ROW_NUMBER() OVER (PARTITION BY [F1],[F2] ORDER BY [F1],[F2]) AS RN
FROM @t_TBB
)
,
TBB_SUM_cte
AS
(
SELECT
[F1]
,[F2]
,[F3]
,RN
,SUM([F3]) OVER(PARTITION BY [F1],[F2] ORDER BY RN,[F1],[F2]) AS SM
FROM TBB_cte
)
SELECT
B.[F1]
,B.[F2]
,B.[F3]
,IIF(A.[F3] IS NOT NULL AND A.[F3] - B.[SM] >=0 , B.[F3],
IIF(A.[F3] IS NOT NULL AND A.[F3] - B.[SM] < 0 AND A.[F3] - LAG(B.[SM],1,1) OVER(PARTITION BY B.[F1],B.[F2] ORDER BY B.[F1],B.[F2],B.RN)>0,
A.[F3] - LAG(B.[SM],1,1) OVER(PARTITION BY B.[F1],B.[F2] ORDER BY B.[F1],B.[F2],B.RN),
0)) [F4]
FROM TBB_SUM_cte B
LEFT JOIN @t_TBA A ON A.[F1] = B.[F1] AND A.[F2] = B.[F2]
/*
F1 F2 F3 F4
----- ----- ----------- -----------
A1 B1 2 2
A1 B1 3 3
A1 B1 3 1
A2 B8 4 0
*/
(
F1 nvarchar(100),
F2 nvarchar(100),
F3 int
)create table 表b
(
F1 nvarchar(100),
F2 nvarchar(100),
F3 int,
F4 int
)insert into 表b
select
'A1','B1' ,2, 0
union
select
'A2','B8',4, 0
union
select
'A1','B1',3, 0
with 表b序号
as
(
select ROW_NUMBER() over(PARTITION by F1,F2 order by F1,F2) as 序号,
F1,F2,F3,F4 from 表B
)
,
表BSum
as
(
select 序号,F1,F2,F3,F4,
总和1=(select SUM(F3) from 表b序号 b where a.F1=b.F1 and a.F2=b.F2 and b.序号<A.序号) ,
总和2=(select SUM(F3) from 表b序号 b where a.F1=b.F1 and a.F2=b.F2 and b.序号<=A.序号)
from 表b序号 a
)
select a.F1,a.F2,a.F3,a.F4,a.序号,a.总和1,a.总和2,b.F3 as 分配,
case when b.F3>=总和2 then a.F3
when b.F3<总和2 and b.F3>=总和1 then B.F3-总和1
else
0
end
from 表BSum a
inner join 表A b
on a.F1=b.F1 and a.F2=b.F2
select 表B.*,ISNULL(table1.赋值后的F4,0) F4 from 表B
left join
(select 表B.F1,表B.F2,表B.F3,表A.F3 as 赋值后的F4 from 表A,表B where 表A.F1=表B.F1 and 表A.F2=表B.F2) as table1
on 表B.F1=table1.F1 and 表B.F2=table1.F2 and 表B.F3=table1.F3