MySQL查询异常：Director字段仅导演获奖时显示，其余为NULL

问题根源在于原查询中，导演信息的获取依赖于当前获奖人员是否为导演——只有当获奖人员是导演时，才能通过关联的ST_peoplefilms记录拿到导演信息，否则返回null。要实现无论获奖类型，始终显示对应电影的导演，需要单独提取每个电影的导演信息，再与主查询关联。

修改后的查询语句如下：

SELECT 
    `MT_films`.`ID_films` AS `ID_films`,
    `ST_br_persons`.`etos_brp` AS `year_award`,  
    `T_brab_p`.`brabeio_p` AS `price`,
    `T_thesmos`.`thesmos` AS `festival`,
    `T_idiotita`.`idiotita` AS `specialty`,
    CONCAT('<a href="./index.php/search-movies/titlededj?id_films=',
            `MT_films`.`ID_films`,
            '">',
            `MT_films`.`Title1`,
            '</a>') AS `movie`,
    GROUP_CONCAT(DISTINCT '<a href="./index.php/search-movies/peoplefilm?person=',
        `ST_br_persons`.`ID_person`,
        '">',
        `T_people`.`person`,
        '</a>'
        SEPARATOR ', ') AS `awarded_person`,
    `film_directors`.`directors` AS `Director`
FROM
    ((((((`T_people` `T_people_1`
    JOIN `ST_peoplefilms` `ST_peoplefilms_1` ON (`T_people_1`.`ID_person` = `ST_peoplefilms_1`.`ID_person`))
    JOIN `MT_films` ON (`MT_films`.`ID_films` = `ST_peoplefilms_1`.`ID_films`))
    JOIN `ST_br_persons` ON (`ST_br_persons`.`ID_person` = `T_people_1`.`ID_person`))
    JOIN `T_idiotita` ON (`T_idiotita`.`ID_idiotita` = `ST_br_persons`.`ID_Idiotita`))
    JOIN `T_thesmos` ON (`T_thesmos`.`ID_thesmos` = `ST_br_persons`.`ID_thesmos`))
    JOIN `T_brab_p` ON (`T_brab_p`.`ID_brab_p` = `ST_br_persons`.`ID_brabeio`))
    JOIN `T_people` ON (`T_people`.`ID_person` = `ST_br_persons`.`ID_person`)
    LEFT JOIN (
        -- 单独提取每个电影的导演信息
        SELECT 
            pf.ID_films,
            GROUP_CONCAT(DISTINCT CONCAT('<a href="./index.php/search-movies/peoplefilm?person=', pf.ID_person, '">', p.person, '</a>') SEPARATOR ', ') AS directors
        FROM ST_peoplefilms pf
        JOIN T_people p ON pf.ID_person = p.ID_person
        WHERE pf.ID_idiotita = 29 -- 导演身份筛选
        GROUP BY pf.ID_films
    ) AS film_directors ON `MT_films`.`ID_films` = `film_directors`.`ID_films`
WHERE
    `ST_br_persons`.`ID_thesmos` IN (12 , 270, 446, 447, 448, 449, 565, 566)
        AND `ST_br_persons`.`ID_Films` = `ST_peoplefilms_1`.`ID_films`
        AND `MT_films`.`ID_films` = 12429
GROUP BY `ST_br_persons`.`ID_brabeio` , `ST_br_persons`.`ID_Films`
ORDER BY `ST_br_persons`.`year_award`

关键修改点 #

1. 新增导演信息派生表：通过子查询film_directors，提前统计每个电影对应的所有导演（筛选ID_idiotita=29），并拼接成带链接的字符串。
2. LEFT JOIN关联导演表：将主查询与film_directors通过电影ID关联，确保每条获奖记录都能拿到对应电影的导演信息，不受当前获奖人员身份影响。
3. 替换Director字段：直接使用film_directors.directors作为最终的Director字段值，替代原查询中依赖获奖人员的CASE逻辑。

内容的提问来源于stack exchange，提问作者Kostas Konstantinidis