查询两个表，但根据字段的值选择不同的列

时间：2022-10-25 22:14:17

I have two tables which contain orders. I need to check that the tables match up. The table look something like below,

我有两个包含订单的表。我需要检查表格是否匹配。表格如下所示,

tblOrders_Sent

Side   Tag     FxBuy  BuyAmount   FxCost CostAmount
B      CHFEUR  CHF    50          EUR    0
B      EURSEK  SEK    75          EUR    0
B      EURNOK  NOK    35          EUR    0
B      DKKEUR  DKK    20          EUR    0
S      CHFEUR  EUR    0           CHF    10    
S      EURSEK  EUR    0           SEK    20
S      EURNOK  EUR    0           NOK    40

tblOrders_Recieved

Tag        FxBuy  MktBuy    FxCost MktCost
EURNOK     NOK    35        EUR    0
DKKEUR     DKK    20        EUR    0
CHFEUR     CHF    50        EUR    0
EURSEK     SEK    75        EUR    0
EURNOK     EUR    0         NOK    40
EURSEK     EUR    0         SEK    20
CHFEUR     EUR    0         CHF    10

When the Side is "B" in the Orders_Sent table I need to check the BuyAmount against the MktBuy amount in the Orders_Recieved table. If the Side is "S" though I need to check the CostAmount vs the MktCost in the Orders_Recieved table. Is this possible or do I need to split the query into two seperate queries?

当Orders_Sent表中的Side为“B”时,我需要根据Orders_Recieved表中的MktBuy金额检查BuyAmount。如果Side是“S”,虽然我需要检查Orders_Recieved表中的CostAmount与MktCost。这是可能的还是我需要将查询分成两个单独的查询?

I'm using SQL Server 2012

我正在使用SQL Server 2012

Desired Output

FxBuy   FxCost   SentAmt   RecievedAmt  Diff
CHF     EUR      50        50           0
SEK     EUR      75        75           0
NOK     EUR      35        35           0
DKK     EUR      20        20           0
EUR     CHF      10        10           0
EUR     SEK      20        20           0
EUR     NOK      40        40           0

1 个解决方案

#1

1  

The CASE keyword helps there. In your case as far as I understood the example it could look like this:

CASE关键字有帮助。在你的情况下,据我所知的例子,它可能看起来像这样:

select 
    tblOrders_sent.fxBuy As FxBuy,
    tblorders_sent.fxCost As FxCost,
    tblorders_sent.buyamount as SentAmt,
    tblorders_received.mktbuy as ReceivedAmt,
    CASE Side WHEN 'B' THEN tblorders_sent.buyamount - tblorders_received.mktbuy END 
    tblorders_sent.costamount - tblorders_received.mktcost END AS diff
from tblOrders_sent 
inner join tblOrders_received on
    tblorders_sent.tag = tblorders_received.tag

#1

1  

The CASE keyword helps there. In your case as far as I understood the example it could look like this:

CASE关键字有帮助。在你的情况下,据我所知的例子,它可能看起来像这样:

select 
    tblOrders_sent.fxBuy As FxBuy,
    tblorders_sent.fxCost As FxCost,
    tblorders_sent.buyamount as SentAmt,
    tblorders_received.mktbuy as ReceivedAmt,
    CASE Side WHEN 'B' THEN tblorders_sent.buyamount - tblorders_received.mktbuy END 
    tblorders_sent.costamount - tblorders_received.mktcost END AS diff
from tblOrders_sent 
inner join tblOrders_received on
    tblorders_sent.tag = tblorders_received.tag
标签：

相关文章