mysql - 用公共(public)列缝两个mysql表

我有两张这样的桌子，
表1：

+-------------+--------+--------+--------+
| contract_id | price1 | price2 | price3 |
+-------------+--------+--------+--------+
|           1 |     23 |     45 |     56 |
|           1 |     22 |     21 |    453 |
|           1 |     45 |    564 |    456 |
|           4 |     22 |     21 |    453 |
|           5 |     45 |    564 |    456 |
+-------------+--------+--------+--------+

还有表2，

+-------------+--------+---------+
| contract_id | owner  | address |
+-------------+--------+---------+
|           1 | Me     | Madras  |
|           1 | father | Chennai |
+-------------+--------+---------+

我想把两张桌子缝在一起，结果看起来像，

+-------------+--------+--------+--------+--------+---------+
| contract_id | price1 | price2 | price3 | owner  | address |
+-------------+--------+--------+--------+--------+---------+
|           1 |     23 |     45 |     56 | Me     | Madras  |
|           1 |     22 |     21 |    453 | father | Chennai |
|           1 |     45 |    564 |    456 | NULL   | NULL    |
|           4 |     22 |     21 |    453 | NULL   | NULL    |
|           5 |     45 |    564 |    456 | NULL   | NULL    |
+-------------+--------+--------+--------+--------+---------+

目前，我正在手动遍历第二个表并更新第一个表中的相应行以实现这一点。我发现的另一个方法是做一个外部连接
清除重复的行。有没有更好的方法来实现这个结果？

最佳答案

我们必须在Row_Number.
试试这个：

SELECT a.contract_id, a.price1, a.price2, a.price3, b.owner, b.address
FROM (SELECT contract_id, price1, price2, price3, (@rank := @rank + 1) AS rank
      FROM t1, (SELECT @rank := 0) tmp
     )a
LEFT JOIN
     (SELECT contract_id, owner, address, (@rank1 := @rank1 + 1) AS rank
      FROM t2, (SELECT @rank1 := 0) tmp
     )b
ON a.rank = b.rank
AND a.contract_id = b.contract_id
ORDER BY a.rank ASC;

See this SQLFiddle

关于mysql - 用公共(public)列缝两个mysql表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12003013/