我有一個(gè)問(wèn)題，我想返回一個(gè)表，每個(gè)票證 ID 一行。查詢(xún)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')輸出目標(biāo)是返回一個(gè)具有唯一工單 ID 的表，如果工單 ID 中有消息或升級(jí)消息，則將其返回到列中。如果沒(méi)有，返回一個(gè)-. 如果工單 ID 升級(jí)并顯示消息，則正確輸出為一行 3 列，如圖所示。
1 回答
達(dá)令說(shuō)
TA貢獻(xiàn)1821條經(jīng)驗(yàn) 獲得超6個(gè)贊
嘗試這個(gè)：
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 關(guān)注
- 128 瀏覽
添加回答
舉報(bào)
0/150
提交
取消