mysql中的node_id不同_mysql从字符串列表中获取不同的标记

我终于可以使用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