我有一個問題，我想返回一個表，每個票證 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 列，如圖所示。
1 回答
達令說
TA貢獻1821條經驗 獲得超6個贊
嘗試這個：
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')
- 1 回答
- 0 關注
- 122 瀏覽
添加回答
舉報
0/150
提交
取消