为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:
【操作系统】:
【CPU】:
【问题描述】:GROUP_CONCAT函数,MySQL迁移过来无法执行,具体是这行GROUP_CONCAT( DISTINCT c.cruiseRouteName ORDER BY c.cruiseRouteName ) AS cruiseRouteNames,以下是SQL,有知道的么:
SELECT
p.,
s.id screenId,
s.screenName,
s.screenData,
GROUP_CONCAT( DISTINCT c.cruiseRouteName ORDER BY c.cruiseRouteName ) AS cruiseRouteNames
FROM
tb_preset p
LEFT JOIN tb_screenArea s ON
p.id = s.presetIndexId
LEFT JOIN tb_cruise c ON
FIND_IN_SET( p.presetId,
c.presetIds )
AND c.ipcId = p.ipcId
WHERE
p.ipcId = 1377
GROUP BY
p.presetId
ORDER BY
p.presetId;
你可以试一下改成使用WM_CONCAT函数,WM_CONCAT( DISTINCT c.cruiseRouteName ORDER BY c.cruiseRouteName )
--使用listagg函数替代
SELECT task_id AS taskId,
listagg(col_name) within group(order by col_name desc) AS colNames
FROM xxx
WHERE 1 = 1
AND id = 0
AND task_id LIKE '%str%'
AND create_time LIKE '%str%'
GROUP BY task_id, data_uuid
ORDER BY task_id, data_uuid
--使用wm_concat函数替代
SELECT task_id AS taskId,
wm_concat(col_name) AS colNames
FROM xxx
WHERE 1 = 1
AND id = 0
AND task_id LIKE '%str%'
AND create_time LIKE '%str%'
GROUP BY task_id, data_uuid