Microsoft SQL代码转MySQL(MariaDB 5.5.68)后无法运行求助

问题现象 #

将一段SQL Server查询代码转换为MariaDB 5.5.68代码后，执行结果中author_1列全为NULL，author_2和author_3列全为空值，无法正确获取作者信息。

原SQL Server代码 #

SELECT t.title_id, t.title_name, p.pub_name,
    (SELECT a.au_fname + ' ' + a.au_lname from authors a
    join title_authors ta on a.au_id = ta.au_id
    where ta.au_order = 1 and ta.title_id = t.title_id)
    as author_1, isnull ((
    select a.au_fname + ' ' + a.au_lname from authors a
    join title_authors ta on a.au_id = ta.au_id
    where ta.au_order = 2 and ta.title_id = t.title_id), '')
    as author_2, isnull((
    select a.au_fname + ' ' + a.au_lname from authors a
    join title_authors ta on a.au_id = ta.au_id
    where ta.au_order = 3 and ta.title_id = t.title_id), '')
    as author_3 from titles t
    join publishers p
    on p.pub_id = t.pub_id;

错误的MariaDB代码 #

SELECT t.title_id, t.title_name, p.pub_name,
    (SELECT CONCAT(a.au_fname, ' ', a.au_lname)
        from authors a
        JOIN title_authors ta ON a.au_id = ta.au_id
        WHERE ta.au_order = 1 and ta.au_id = t.title_id) as author_1,
    ISNULL((
        SELECT CONCAT(a.au_fname, a.au_lname)
        from authors a
        JOIN title_authors ta ON a.au_id = ta.au_id
        WHERE ta.au_order = 2 and ta.au_id = t.title_id),'') as author_2,
    ISNULL((
        SELECT CONCAT(a.au_fname, a.au_lname)
        from authors a
        JOIN title_authors ta ON a.au_id = ta.au_id
        WHERE ta.au_order = 3 and ta.au_id = t.title_id),'') as author_3
FROM titles t
JOIN publishers p
ON p.pub_id = t.pub_id;

问题分析与修复 #

以下是导致异常的核心问题及修正方案：

• 关联条件错误：所有子查询中的关联条件写成了ta.au_id = t.title_id，这是字段混淆——应该是ta.title_id = t.title_id（书籍ID关联），而非作者ID和书籍ID关联，这直接导致无法匹配到数据。
• 函数用法错误：MariaDB的ISNULL()仅接受单个参数，用于判断值是否为NULL；要实现SQL Server中ISNULL(表达式, 默认值)的功能，需替换为IFNULL()。
• 字符串拼接缺失空格：author_2和author_3的CONCAT函数中缺少空格，导致作者名连在一起，需补上' '。

正确的MariaDB代码 #

SELECT t.title_id, t.title_name, p.pub_name,
    (SELECT CONCAT(a.au_fname, ' ', a.au_lname)
     FROM authors a
     JOIN title_authors ta ON a.au_id = ta.au_id
     WHERE ta.au_order = 1 AND ta.title_id = t.title_id) AS author_1,
    IFNULL((
        SELECT CONCAT(a.au_fname, ' ', a.au_lname)
        FROM authors a
        JOIN title_authors ta ON a.au_id = ta.au_id
        WHERE ta.au_order = 2 AND ta.title_id = t.title_id
    ), '') AS author_2,
    IFNULL((
        SELECT CONCAT(a.au_fname, ' ', a.au_lname)
        FROM authors a
        JOIN title_authors ta ON a.au_id = ta.au_id
        WHERE ta.au_order = 3 AND ta.title_id = t.title_id
    ), '') AS author_3
FROM titles t
JOIN publishers p ON p.pub_id = t.pub_id;

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