php - 连接两个表，如果列不存在，则在列中显示 null

Question

我有两个表，如下。

阶段:

stageid     stagename     is_corrected
   1           abc              1
   2           xyz              1
   3           aaa              0
   4           bbb              1

回复:

stageid     teamid      diffscore      
   1           1            10
   1           2            12
   1           3            15
   2           1            12
   2           2            13
   2           3            16
   2           4            14

我正在尝试连接它们并显示一个连接表，其中 is_ Corrected = 1。这是我尝试的查询:

查询:

SELECT 
    t1.stagename, 
    t2.diffscore 
FROM 
    stages t1 
    LEFT OUTER JOIN (
        SELECT 
            diffscore, 
            teamid, 
            stageid 
        FROM 
            responses as t2
    ) as t2 ON t2.stageid = t1.stageid 
    OR t1.stageid = NULL 
WHERE 
    t1.is_corrected = 1 
    AND (
        t2.teamid = 4 
        OR t2.teamid = NULL
    )

预期结果:

stagename      diffscore
   abc            NULL
   xyz            14
   bbb            NULL

输出:

stagename      diffscore
   xyz            14

Answer 1

试试这个:

SELECT 
    s.stageid, 
    s.stagename, 
    IFNULL(
        (
            SELECT 
                diffscore 
            FROM 
                responses 
            WHERE 
                s.stageid = stageid 
                AND (
                    teamid = 1
                    OR teamid IS NULL
                )
            LIMIT 1
        ), 
        0
    ) AS diffscore 
FROM 
    stages s 
WHERE 
    is_corrected = 1