从表中删除重复记录并返回 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 message

FROM

    tickets

    INNER JOIN ticketnotes ON ticketnotes.ticketid = tickets.id

WHERE

    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')