php - MySQL选择一行中的多行

Question

我想要这样的东西，但不确定我的语法是否正确。我将使用 php 执行此操作。

SELECT 
    a.column1,
    a.column2,
    b.column1,
    c.column1,
    IF a.column3 NOT NULL
        THEN (
            SELECT c.column1, c.column2, c.column3, d.column1
            FROM table_d d
            INNER JOIN table_c c
                ON d.column1 = c.column1 AND c.column4 = 1
            WHERE d.column2 = a.column3
        );
    END IF;

FROM table_a a

    INNER JOIN table_b b
        ON a.column1 = b.column1 AND b.column2 = 1

    INNER JOIN table_c c
        ON a.column1 = c.column1 AND c.column2 = 1

WHERE
    a.column1 = 1000
    AND b.column3 = 1
    AND c.column3 = 0

ORDER BY 
        a.column1 ASC

所以输出将是这样的:

如果它有多行且前几列的数据相同就可以了。像这样:

灰色区域来自outer SELECT，白色区域来自inner SELECT

注意外层和内层select语句都有table_c。如果没有 IF 语句，我可以这样做吗？

SELECT 
    a.column1,
    a.column2,
    b.column1,
    c.column1,
    cc.column1,
    cc.column2,
    cc.column3,
    d.column1   

FROM table_a a

    INNER JOIN table_b b
        ON a.column1 = b.column1 AND b.column2 = 1

    INNER JOIN table_c c
        ON a.column1 = c.column1 AND c.column2 = 1

    LEFT JOIN table_d d
        ON  a.column3 = d.column2

    INNER JOIN table_c cc
        ON d.column1 = cc.column1 AND cc.column4 = 1

WHERE
    a.column1 = 1000
    AND b.column3 = 1
    AND c.column3 = 0

ORDER BY 
        a.column1 ASC

我觉得有点不对劲。如果我使用 fetch_assoc 会怎样？甚至可以在一个查询中执行此操作吗？

Answer 1

尝试这样的事情:

SELECT 
    a.column1,
    a.column2,
    b.column1,
    c.column1,
    IF (a.column3 NOT NULL,c_2.column1,''),
    IF (a.column3 NOT NULL,c_2.column2,''),
    IF (a.column3 NOT NULL,c_2.column3,''),
    IF (a.column3 NOT NULL,d.column4,'')

FROM table_a a

    INNER JOIN table_b b
        ON a.column1 = b.column1 AND b.column2 = 1

    INNER JOIN table_c c
        ON a.column1 = c.column1 AND c.column2 = 1

    INNER JOIN table_c as c_2
        ON d.column1 = c.column1 AND c.column4 = 1

    INNER JOIN table_d 
        ON a.column1 = c.column1 AND c.column2 = 1
WHERE
    a.column1 = 1000
    AND b.column3 = 1
    AND c.column3 = 0
ORDER BY 
        a.column1 ASC