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

Question

我有两个这样的表，

表 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    |
+-------------+--------+--------+--------+--------+---------+

目前我正在手动遍历第二个表并更新第一个表中的相应行以实现此目的。我想出的另一种方法是进行外部连接和清理重复的行。是否有更好的方法来实现结果？

Answer 1

这应该可以完美运行。首先，我计算两个表的排名，然后根据排名使用 LEFT JOIN 连接这两个表，这样输出中就不会出现任何重复项，因为它是一对一的映射。

试试这个查询:

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 
         table1, (SELECT @rank := 0) tmp) a
LEFT JOIN 
    (SELECT 
         contract_id, owner, address, (@rank := @rank + 1) AS rank
     FROM 
         table2, (SELECT @rank := 0) tmp) b ON a.rank = b.rank
ORDER BY 
    a.rank ASC;

编辑更准确的查询:您需要为匹配的 contract_ids 创建嵌套排名:

SELECT 
    a.c_id, a.price1, a.price2, a.price3, b.owner, b.address
FROM 
    (SELECT 
         @var_rank := IF(contract_id <> @var_id_prev,1, @var_rank+1) AS vrank,
         @var_id_prev := contract_id AS c_id,
         price1, price2, price3, @rank := (@rank + 1) AS rnk
     FROM 
         table1, (SELECT @var_id_prev := 0) tmp
     ORDER BY 
         contract_id) a
LEFT JOIN 
    (SELECT 
         @var_rank := IF(contract_id <> @var_id_prev,  1, 
         @var_rank+1) AS vrank,
         @var_id_prev := contract_id AS c_id,
         owner, address, @rank := @rank + 1 AS rnk
     FROM 
         table2, (SELECT @var_id_prev := 0) tmp
     ORDER BY 
         contract_id) b ON a.c_id = b.c_id AND a.vrank = b.vrank
ORDER BY 
    a.c_id, a.vrank ASC;

SQL FIDDLE DEMO HERE

但更好的方法是将 auto id 放在两个表上并在连接条件中使用它。

通过这样做，表被规范化，并且在计算排名时将节省在内存中创建临时表的额外开销。它还将通过在这些连接列上使用索引来加速查询。