我有一个MSSQL数据库,想要将其转换/迁移到MySQL。
我遇到了以下错误信息:
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') FROM (SELECT a3t' at line 10
SELECT DISTINCT task_id, members = STUFF(( SELECT N', ' + t.Ful FROM (SELECT teamAlpha.task_id, ( users.firstname + ' ' + users.lastname ) as Ful FROM teamAlpha JOIN users ON users.user_id = teamAlpha.user_id ) t WHERE t.task_id = u.task_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') FROM (SELECT teamAlpha.task_id, ( users.firstname + ' ' + users.lastname ) as Ful FROM teamAlpha JOIN users ON users.user_id = teamAlpha.user_id ) u
以下是代码:
这是代码:
$query = $this->db->query("
SELECT DISTINCT
task_id,
members = STUFF((
SELECT N', ' + t.Ful
FROM (SELECT teamAlpha.task_id,
( users.firstname + ' ' + users.lastname ) as Ful
FROM teamAlpha
JOIN users ON users.user_id = teamAlpha.user_id ) t
WHERE t.task_id = u.task_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM
(SELECT teamAlpha.task_id,
( users.firstname + ' ' + users.lastname ) as Ful
FROM teamAlpha
JOIN users ON users.user_id = teamAlpha.user_id ) u");
return $query->result();
在MySQL中,STUFF()和XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''的等效函数是什么?非常感谢您的帮助。