从不同的列和两个表中选择数据 [英] Select data from different columns and two tables

问题描述

我已经问(并解决)了一些问题 此处 但是现在更复杂了.我有相同的BID表:

I already asked (and solved) something close here but now is more complicated. I have the same BID table:

Id_auction        bidder_1      winner_1    bidder_2    winner_2    item
  1                Alice           1          Ben         1          cup
  2               Charles          0          Alice       1          mug
  3                 Ben            1          Charles     1          pen

但是现在我想将信息与另一个表USD结合起来

But now I want to join the information with another table USD

Id_auction         USD
1                  100
2                  150
3                   50

如果投标人是中标者，则值为1；如果不是中标者，则值为0.因此，在第一次拍卖中，爱丽丝和本是中标者，在第二次拍卖中，只有爱丽丝获胜.我将需要一个MySQL查询，因此结果将如下所示:

If a bidder is a winner the value is 1 if is not a winner the value is 0. So, in first auction, Alice and Ben are winners, in the second only Alice wins. I will need a MySQL query so the results will be like this:

  Id_auction       bidder        item      bidder_number   USD
  1                Alice         cup            1          100
  1                Ben           cup            2          100
  2                Alice         mug            2          150
  3                Ben           pen            1           50
  3                Charles       pen            2           50

谢谢！

推荐答案

仅JOIN与此表USD:

SELECT sub.*, USD.USD
FROM
(
  SELECT 
    t1.id_auction,
    t2.bidder_1 AS bidder,
    t2.item,
    1 AS bidder_number    
  FROM table1 t1
  INNER JOIN table1 t2  ON t1.id_auction = t2.id_auction
                       AND t2.winner_1 = 1
  UNION ALL
  SELECT 
    t1.id_auction,
    t2.bidder_2 AS bidder,
    t2.item,
    2                    
  FROM table1 t1
  INNER JOIN table1 t2  ON t1.id_auction = t2.id_auction
                       AND t2.winner_2 = 1
) AS sub
INNER JOIN USD ON sub.id_auction = USD.id_auction
ORDER BY id_auction, bidder;

> 更新的SQL Fiddle演示

| ID_AUCTION |  BIDDER | ITEM | BIDDER_NUMBER | USD |
-----------------------------------------------------
|          1 |   Alice |  cup |             1 | 100 |
|          1 |     Ben |  cup |             2 | 100 |
|          2 |   Alice |  mug |             2 | 150 |
|          3 |     Ben |  pen |             1 |  50 |
|          3 | Charles |  pen |             2 |  50 |

这篇关于从不同的列和两个表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！