mysql 多表查询

Question

table A

id	a
1	a
2	b

---

table B

id	b
1	a
3	c

---

table C

id	c
1	a
2	b
3	c

---

---

table D

id	d
1	a
3	b
4	c

---

查询后的结果

id	a	b	c	d
1	a	a	a	a
2	b		b
3		c	c	b
4				c

请问SQL 应该怎么写? 谢谢

Answer 1

left join

Answer 2

题主你的题目好难啊，我搞了大半天终于调出来了，我sql不熟，感觉像逻辑题
代码如下：

SELECT
    *
FROM
    (
        SELECT
            *
        FROM
            (
                SELECT
                    *
                FROM
                    a
                INNER JOIN b USING (id)
                INNER JOIN c USING (id)
                INNER JOIN d USING (id)
            ) e
        UNION
            SELECT
                id,
                a,
                NULL,
                c,
                NULL
            FROM
                a
            INNER JOIN c USING (id)
            UNION
                SELECT
                    id,
                    NULL,
                    b,
                    c,
                    d
                FROM
                    b
                INNER JOIN c USING (id)
                INNER JOIN d USING (id)
                UNION
                    SELECT
                        id,
                        a,
                        NULL,
                        NULL,
                        d
                    FROM
                        a
                    RIGHT OUTER JOIN d USING (id)
    ) f
GROUP BY
    id