MySQL将两个查询合并为一个查询 [英] MySQL combining two queries into single query

问题描述

我的第一个查询如下所示

My first query looks as below

SELECT a.name, b.desc, T3.desc1 as Output
FROM table1 a INNER JOIN
     table2 b
     ON a.id = b.id
INNER JOIN (
  SELECT b2.id, b2.status,  MAX(b2.created_time) as max_time
  FROM oac.qualys_scan b2
  GROUP BY b2.id  ,  b2.qualys_type
  ) t on t.id = a.id
      AND  t.status=b.status
      AND t.max_time = b.created_time
INNER JOIN table3 T3 on b.tid = T3.tid
WHERE b.status = 'FAIL'

我的第二个查询如下所示

My second query looks as below

SELECT a.name, b.desc, T4.desc2 as Output
FROM table1 a INNER JOIN
     table2 b
     ON a.id = b.id
INNER JOIN (
  SELECT b2.id, b2.status,  MAX(b2.created_time) as max_time
  FROM oac.qualys_scan b2
  GROUP BY b2.id  ,  b2.qualys_type
  ) t on t.id = a.id
      AND  t.status=b.status
      AND t.max_time = b.created_time
INNER JOIN table4 T4 on b.tid = T4.tid
WHERE b.status = 'FAIL'

在我的最终结果中，我想要Output column，其值将为T3.desc1或T4.desc2 如何将两个查询合并为一个查询?

In my final result I want Output column which will have values either from T3.desc1 or T4.desc2 How can I combine both queries into a single query?

推荐答案

如果table2.id存在于表-table3或table4之一中，则LEFT JOIN将借助IFNULL().

If the table2.id exists in one of the tables - table3 or table4, a LEFT JOIN will simplify the query with the help of IFNULL().

SELECT  a.name, 
        b.desc, 
        IFNULL(T3.desc1, T4.desc2) as Output
FROM    table1 a 
        INNER JOIN table2 b ON a.id = b.id
        INNER JOIN 
        (
            SELECT b2.id, b2.status,  MAX(b2.created_time) as max_time
            FROM oac.qualys_scan b2
            GROUP BY b2.id  ,  b2.qualys_type
        ) t on t.id = a.id
              AND  t.status=b.status
              AND t.max_time = b.created_time
        LEFT JOIN table3 T3 on b.tid = T3.tid
        LEFT JOIN table4 T4 on b.tid = T4.tid
WHERE   b.status = 'FAIL'

这篇关于MySQL将两个查询合并为一个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！