从表中删除重复记录并返回 3 列

Question

我有一个问题，我想返回一个表，每个票证 ID 一行。查询SELECT    tickets.tid,    CASE WHEN ticketnotes.message             LIKE '%https://xxxx.zendesk.net/.../%'             THEN ticketnotes.message             ELSE '-' END as escalated,    CASE WHEN ticketnotes.message             LIKE '%Ticket ID%Issue Summary%Suggested Solution%'             THEN ticketnotes.message             ELSE '-' END as messageFROM    tickets    INNER JOIN ticketnotes ON ticketnotes.ticketid = tickets.idWHERE    tickets.status = 'Closed'     AND tickets.did = 7    AND DATE(tickets.date) BETWEEN DATE('2020-04-01') AND DATE('2020-04-30')输出目标是返回一个具有唯一工单 ID 的表，如果工单 ID 中有消息或升级消息，则将其返回到列中。如果没有，返回一个-. 如果工单 ID 升级并显示消息，则正确输出为一行 3 列，如图所示。

Answer 1

尝试这个：

SELECT

    distinct t1.tid,

    t2.message as "escalated",

    t3.message as "message"

FROM

    tickets t1

    left join (select ticketid, message from ticketnotes where message LIKE '%https://xxxx.zendesk.net/.../%') t2 on t2.ticketid = t1.tid 

    left join (select ticketid, message from ticketnotes where message LIKE '%Ticket ID%Issue Summary%Suggested Solution%') t3 on t3.ticketid = t1.tid 

WHERE

    t1.status = 'Closed' 

    AND t1.did = 7

    AND DATE(t1.date) BETWEEN DATE('2020-04-01') AND DATE('2020-04-30')