现在要写一个存储过程,大概情况是这样:
A表
NO NAME PRICE(RMB)
1 AA 10
2 BB 20
3 CC 30B表
NO NAME PRICE(USD)
1 AA 20
3 CC 30
4 DD 50C表
NO
1D表
NO RATE
1 6
现在是要一个存储过程,结果是
NO NAME PRICE(RMB) PRICE(USD) RATE PRICE(USD-RMB)
1 AA 10 20 6 120
2 BB 20 30 1 30
3 CC 30 0 1 0
4 DD 0 50 1 50
条件有:A表B表全连接后得到的NO号,去C表遍历一下,如果A表B表的NO号在C表中存在,则,使用D表的汇率去计算PRICE(USD-RMB)求高手解答
A表
NO NAME PRICE(RMB)
1 AA 10
2 BB 20
3 CC 30B表
NO NAME PRICE(USD)
1 AA 20
3 CC 30
4 DD 50C表
NO
1D表
NO RATE
1 6
现在是要一个存储过程,结果是
NO NAME PRICE(RMB) PRICE(USD) RATE PRICE(USD-RMB)
1 AA 10 20 6 120
2 BB 20 30 1 30
3 CC 30 0 1 0
4 DD 0 50 1 50
条件有:A表B表全连接后得到的NO号,去C表遍历一下,如果A表B表的NO号在C表中存在,则,使用D表的汇率去计算PRICE(USD-RMB)求高手解答
create proc p_test
asselect isnull(a.NO,b.NO) NO,
isnull(a.NAME,b.NAME) NAME,
isnull(a.[PRICE(RMB)],0) [PRICE(RMB)],
isnull(b.[PRICE(RMB)],0) [PRICE(USD)],
isnull(D.RATE,1) RATE,
[PRICE(USD-RMB)]=isnull(b.[PRICE(RMB)],0)*isnull(D.RATE,1)-isnull(a.[PRICE(RMB)],0)
from
( A left join C on c.NO=A.NO left join D on C.NO=D.NO ) full join
( B left join C on c.NO=B.NO left join D on C.NO=D.NO ) on A.NO=B.NO
declare @a table(NO INT, NAME VARCHAR(20), [PRICE(RMB)] int)
insert @A select 1 ,'AA', 10
UNION ALL select 2 ,'BB', 20
UNION ALL select 3 ,'CC', 30
declare @B table(NO INT, NAME VARCHAR(20), [PRICE(USD)] int)
insert @B select 1 ,'AA', 20
UNION ALL select 3 ,'CC', 30
UNION ALL select 4 ,'DD', 50
declare @c table(NO INT)
insert @C select 1
declare @d table(NO INT, RATE INT)
insert @d select 1, 6SELECT ISNULL(A.NO,B.NO) NO,
ISNULL(A.NAME,B.NAME) NAME,
ISNULL(A.[PRICE(RMB)],0) [PRICE(RMB)],
ISNULL(B.[PRICE(USD)],0) [PRICE(USD)],
ISNULL(RATE,1) RATE,
ISNULL([PRICE(USD)],0)*ISNULL(RATE,1) [PRICE(RMB-USD)]
FROM @A A FULL JOIN @B B ON A.NO=B.NO LEFT JOIN @C C ON A.NO=C.NO OR B.NO=C.NO
LEFT JOIN @D D ON C.NO=D.NO
ORDER BY 1--RESULT
/*
NO NAME PRICE(RMB) PRICE(USD) RATE PRICE(RMB-USD)
----------- -------------------- ----------- ----------- ----------- --------------
1 AA 10 20 6 120
2 BB 20 0 1 0
3 CC 30 30 1 30
4 DD 0 50 1 50(所影响的行数为 4 行)*/
ISNULL(A.NAME,B.NAME) NAME,
ISNULL(A.[PRICE(RMB)],0) [PRICE(RMB)],
ISNULL(B.[PRICE(USD)],0) [PRICE(USD)],
ISNULL(RATE,1) RATE,
ISNULL([PRICE(USD)],0)*ISNULL(RATE,1) [PRICE(RMB-USD)]
FROM A FULL JOIN B ON A.NO = B.NO
LEFT JOIN C ON A.NO = C.NO OR B.NO = C.NO
LEFT JOIN D ON C.NO = D.NO