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

velaa5lx 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(192)

我有一个问题，我想返回一个表，每个票id一行。
SQL查询

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列，如图所示。

sql mysql php mariadb

来源：https://stackoverflow.com/questions/61959432/remove-duplicate-records-from-table-and-return-3-columns

1条答案

按热度按时间

hk8txs481#

试试这个：

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

赞(0）回复(0）举报 2021-08-13

我来回答

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

1条答案

相关问题

热门标签

最新问答