时间戳

首页 > 解决方案 > 如何从 2 个查询中进行简单查询

sql - 如何从 2 个查询中进行简单查询

问题描述

我有点坚持从 2 个查询中获取一个简单的查询，并希望生成这样的数据

输出示例图片

#查询1：

select b.number as ticket_id,
    d.name as reported_by,
    b.created as reported_date,
    f.topic as help_topic,
    c.subject,
    e.priority,
    h.created as l1_escalated_date,
    h.poster  as l1_escalated_by
from ost_staff a, 
    ost_ticket b, 
    ost_ticket__cdata c, 
    ost_user d, 
    ost_ticket_priority e,
    ost_help_topic f,
    ost_ticket_status g,
    ost_thread_entry h
where b.isanswered ='1' 
    and a.staff_id = b.staff_id 
    and b.ticket_id = c.ticket_id 
    and b.user_id = d.id
    and c.priority = e.priority_id 
    and b.topic_id = f.topic_id 
    and b.status_id = g.id 
    and b.ticket_id = h.thread_id 
    and (h.body like '%ESC1%' or h.body like '%RPN1%') 
group by b.number

#查询2：

select b.number as ticket_id,
    d.name as reported_by,
    b.created as reported_date,
    f.topic as help_topic,
    c.subject,
    e.priority,
    h.created as l2_escalated_date,
    h.poster as l2_escalated_by
from ost_staff a, 
    ost_ticket b, 
    ost_ticket__cdata c, 
    ost_user d, 
    ost_ticket_priority e,
    ost_help_topic f,
    ost_ticket_status g,
    ost_thread_entry h
where b.isanswered ='1' 
    and a.staff_id = b.staff_id 
    and b.ticket_id = c.ticket_id 
    and b.user_id = d.id
    and c.priority = e.priority_id 
    and b.topic_id = f.topic_id 
    and b.status_id = g.id 
    and b.ticket_id = h.thread_id 
    and (h.body like '%ITL2%' or h.body like '%ITR2%') 
group by b.number

主要区别是

标签： sql

解决方案

在这种情况下，您不能使用 UNION，因为列名不同，但您可以使用 CASE 语句根据某些条件获取数据。

SELECT b.number AS ticket_id
    ,d.name AS reported_by
    ,b.created AS reported_date
    ,f.topic AS help_topic
    ,c.subject
    ,e.priority
    ,l1_escalated_date = (CASE h.created 
        WHEN h.body LIKE '%ITL2%' OR h.body LIKE '%ITR2%'
            THEN h.created
        ELSE 
            NULL
        END  )
    ,l1_escalated_by =  (CASE h.poster
        WHEN h.body LIKE '%ITL2%' OR h.body LIKE '%ITR2%'
            THEN h.created
        ELSE 
            NULL
        END  )          
    ,l2_escalated_date = (CASE h.created 
        WHEN h.body LIKE '%ESC1%' OR h.body LIKE '%RPN1%'
            THEN h.created
        ELSE 
            NULL 
        END )
    ,l2_escalated_by =  (CASE h.poster
        WHEN h.body LIKE '%ESC1%' OR h.body LIKE '%RPN1%'
            THEN h.created
        ELSE 
            NULL 
        END )
FROM ost_staff a
    ,ost_ticket b
    ,ost_ticket__cdata c
    ,ost_user d
    ,ost_ticket_priority e
    ,ost_help_topic f
    ,ost_ticket_status g
    ,ost_thread_entry h
WHERE b.isanswered = '1'
    AND a.staff_id = b.staff_id
    AND b.ticket_id = c.ticket_id
    AND b.user_id = d.id
    AND c.priority = e.priority_id
    AND b.topic_id = f.topic_id
    AND b.status_id = g.id
    AND b.ticket_id = h.thread_id
    AND (
        h.body LIKE '%ITL2%'
        OR h.body LIKE '%ITR2%'
        OR h.body LIKE '%ESC1%'
        OR h.body LIKE '%RPN1%'
        )
GROUP BY b.number

推荐阅读