Microsoft SQL代码转换为MySQL时不工作

Question

我有一个Microsoft SQL代码块，我正试图在MySQL (MariaDB 5.5.68)中工作。我已经试了一千种方法，但都无济于事。

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;

请帮忙!

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;

这将生成一个表，第一列为NULL，后面两列为空。

Answer 1

@JJ32有答案了。我在比较两个不同的列。下面是工作代码:

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;