我有一个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();
mssql的STUFF()和XML PATH(''),TYPE). value('.',' NVARCHAR(MAX)'),1,1,''在mysql中的等价物是什么?非常感谢您的帮助。
2条答案
按热度按时间bqjvbblv1#
看起来您只想在表中生成与给定任务关联的人员的CSV列表。您可以尝试以下查询:
请注意,必须使用
FOR XML PATH
的丑陋之处消失了。虽然SQL Server对分析函数有丰富的支持,但它的组连接支持不是很好。在SQL Server 2017中有一个新函数STRING_AGG
,它基本上与MySQL的GROUP_CONCAT
做相同的事情。yshpjwxd2#
这很有帮助-谢谢