27,579
社区成员
发帖
与我相关
我的任务
分享
create view c
as
select distinct B,C,A.AID,col=(case when A.AID=B.BID then B.L-E else E end) from A left join B on A.AID=B.BID
/* 测试数据
WITH A(AID,B,C,E) AS (
SELECT 1,'A','D',22 UNION ALL
SELECT 2,'C','E',54 UNION ALL
SELECT 3,'G','B',45 UNION ALL
SELECT 4,'B','X',55 UNION ALL
SELECT 5,'Z','C',5
)
,B(BID,AID,Z,L) AS (
SELECT 1,1,'D',44 UNION ALL
SELECT 2,3,'B',22 UNION ALL
SELECT 3,3,'B',5
) */
SELECT A.AID,
A.B,
A.C,
A.E - ISNULL(SB.L,0) E
FROM A
LEFT JOIN (
SELECT AID,
SUM(L) L
FROM B
GROUP BY AID
) SB
ON A.AID = SB.AID
AID B C E
----------- ---- ---- -----------
1 A D -22
2 C E 54
3 G B 18
4 B X 55
5 Z C 5
create view c
as
select A.AID,col=(case when A.AID=B.BID then B.L-E else E end) from A,B
--求一新表c? 没明白,这样?
update A set E=B.L-E from B where A.AID=B.BID