mysql 加入问题

Question

mysql> select * from new_table;
+-------------+
| idnew_table |
+-------------+
|           1 |
|           2 |
+-------------+

mysql> select * from second_table;
+----------------+--------+
| idsecond_table | second |
+----------------+--------+
|            100 |      1 |
|            150 |      1 |
|            200 |      2 |
+----------------+--------+

mysql> select * from third;
+---------+-------+
| idthird | third |
+---------+-------+
|     500 |     1 |
|     600 |     2 |
+---------+-------+

我需要将它连接到一个，就像这样

+-----+-----+--------+------+------------+
| tid | sid | secsec | thid | thirdthird |
+-----+-----+--------+------+------------+
|   1 | 100 |      1 |  500 |          1 |
|   1 | 150 |      1 | null |       null |
|   2 | 200 |      2 |  600 |          2 |
+-----+-----+--------+------+------------+

我正在尝试这个查询

select 
t.idnew_table as tid,
sec.idsecond_table as sid ,
sec.second as secsec,
th.idthird as thid,
th.third as thirdthird
from
new_table t
join second_table sec on sec.second = t.idnew_table
join third th on th.third = t.idnew_table

，但它重复第三个表中的行，

+-----+-----+--------+------+------------+
| tid | sid | secsec | thid | thirdthird |
+-----+-----+--------+------+------------+
|   1 | 100 |      1 |  500 |          1 |
|   1 | 150 |      1 |  500 |          1 |
|   2 | 200 |      2 |  600 |          2 |
+-----+-----+--------+------+------------+

所以我需要你的建议

Answer 1

根据您的评论和提供的数据，我猜您需要第二个和第三个表的FULL OUTER JOIN，基于行号和ID（second_table.second和< code>third_table.third），然后（通常）INNER JOIN 到第一个表。

不幸的是，表中不存在行号，并且 MYSQL 不提供窗口函数。因此这是可能的，但有这样可怕的事情：

SELECT
    fulljoin.*
FROM new_table t
JOIN
    (
    SELECT
         COALESCE(second,third) AS idnew_table
       , idsecond_table AS sid
       , second AS secsec
       , idthird AS thid
       , third AS thirdthird
    FROM
    (
    SELECT
        @rn := if(@g = second, @rn+1, 1) AS rownumber
      , second
      , idsecond_table
      , @g := second
    FROM
        ( select @g:=null, @rn:=0 ) AS initvars
    CROSS JOIN
        second_table
    ORDER BY
        second
      , idsecond_table
    ) AS grp2
    LEFT JOIN
    (
    SELECT
        @rn := if(@g = third, @rn+1, 1) AS rownumber
      , third
      , idthird
      , @g := third
    FROM
        ( select @g:=null, @rn:=0 ) AS initvars
    CROSS JOIN
        third_table
    ORDER BY
        third
      , idthird
    ) AS grp3
    ON grp2.second = grp3.third
    AND grp2.rownumber = grp3.rownumber

    UNION ALL

    SELECT
         COALESCE(second,third) AS idnew_table
       , idsecond_table AS sid
       , second AS secsec
       , idthird AS thid
       , third AS thirdthird
    FROM
    (
    SELECT
        @rn := if(@g = second, @rn+1, 1) AS rownumber
      , second
      , idsecond_table
      , @g := second
    FROM
        ( select @g:=null, @rn:=0 ) AS initvars
    CROSS JOIN
        second_table
    ORDER BY
        second
      , idsecond_table
    ) AS grp2
    RIGHT JOIN
    (
    SELECT
        @rn := if(@g = third, @rn+1, 1) AS rownumber
      , third
      , idthird
      , @g := third
    FROM
        ( select @g:=null, @rn:=0 ) AS initvars
    CROSS JOIN
        third_table
    ORDER BY
        third
      , idthird
    ) AS grp3
    ON grp2.second = grp3.third
    AND grp2.rownumber = grp3.rownumber
    WHERE grp2.second IS NULL
    ) AS fulljoin
ON fulljoin.idnew_table = t.idnew_table
;