本文介绍了如何结合UNION操作符和GROUP_CONCAT函数来处理复杂的SQL查询，特别是在从多个表中聚合并按特定顺序显示不重复数据的场景下。查询涉及到mst_node和trn_user_log表的联接，通过条件过滤，最终实现按transaction_id排序并显示distinct display_text的有序列表。
我终于可以使用UNION两个选择查询然后执行GROUP_CONCAT(DISTINCT column ORDER BY another_column)来解决我的问题。
以下是我使用的查询：
SELECT
transaction_id,
GROUP_CONCAT(DISTINCT display_text ORDER BY logtime_stamp SEPARATOR '->') AS display_text
FROM
(SELECT
t2.transaction_id AS transaction_id,
t2.logtime_stamp,
t1.display_text AS display_text
FROM mst_node t1
JOIN trn_user_log t2 ON t1.app_id = t2.app_id
AND t1.node_id = t2.node_id
WHERE (t1.app_id = 105)
AND t1.parent_node_id IS NOT NULL
AND t1.save_as_default IS NULL
UNION
SELECT t2.transaction_id AS transaction_id,t2.logtime_stamp,
CASE(NOT EXISTS (SELECT 1 FROM mst_node a WHERE a.parent_node_id = t1.node_id))
WHEN 1 THEN NULL
ELSE (SELECT b.display_text AS display_text FROM mst_node b WHERE parent_node_id = t2.node_id AND b.display_seq = t2.entered_value)
END AS display_text
FROM mst_node t1 JOIN trn_user_log t2
ON t1.app_id = t2.app_id AND t1.node_id = t2.node_id
WHERE (t1.app_id = 105)
AND t1.parent_node_id IS NOT NULL
AND t1.save_as_default IS NULL
ORDER BY transaction_id DESC,logtime_stamp
) AS T
GROUP BY transaction_id
ORDER BY transaction_id DESC,logtime_stamp
扫一扫
1164
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
