mysql - 如何在mysql中左连接3个表

Question

我有 3 张 table :

成员(网卡，名称)

奖项(nic，aw_name)

冠军(nic，ch_name)

一个人可以拥有多个奖项或冠军。例如理查德有2个奖项和3个冠军，而史蒂夫没有任何奖项或冠军。

现在我想列出获得奖项和冠军的人员名单。例如:

|    nic   |    name    |   aw_name   |      ch_name      |
|----------|------------|-------------|-------------------|
| 1        | Richard    | award 1     | championship 1    |
| 1        | Richard    | award 2     | championship 2    |
| 1        | Richard    |             | championship 3    |
| 2        | Steve      |             |                   |

谁能帮帮我吗？谢谢!

Answer 1

试试这个:

SELECT
    members.nic,
    members.name,
    awards.aw_name,
    championships.ch_name
FROM
    (
        SELECT
            CASE WHEN @curNic = members.nic THEN @curRow := @curRow + 1 ELSE @curRow := 1 END num,
            @curNic := members.nic nic,
            name
        FROM
            members LEFT JOIN
            (
                SELECT nic FROM awards UNION ALL
                SELECT nic FROM championships
            ) tmp ON tmp.nic = members.nic JOIN
            (SELECT @curNic := null, @curRow := 0) r
    ) members LEFT JOIN
    (
        SELECT
            CASE WHEN @curNic1 = nic THEN @curRow1 := @curRow1 + 1 ELSE @curRow1 := 1 END num,
            @curNic1 := nic nic,
            aw_name
        FROM
            awards JOIN
            (SELECT @curNic1 := null, @curRow1 := 0) r
    ) awards ON awards.nic = members.nic AND awards.num = members.num LEFT JOIN
    (
        SELECT
            CASE WHEN @curNic2 = nic THEN @curRow2 := @curRow2 + 1 ELSE @curRow2 := 1 END num,
            @curNic2 := nic nic,
            ch_name
        FROM
            championships JOIN
            (SELECT @curNic2 := null, @curRow2 := 0) r
    ) championships ON championships.nic = members.nic AND championships.num = members.num
WHERE
    awards.nic IS NOT NULL OR championships.nic IS NOT NULL OR members.num = 1

如果mysql中有FULL OUTER JOIN会更容易，但是没有。

fiddle :http://www.sqlfiddle.com/#!2/31d47/43