我的第一个查询如下
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'
AND b.type = 'Type1'
我的第二个查询看起来如下
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'
AND b.type = 'Type2'
在我的最终结果中,我想要Output column
,它将具有T3.desc1
或T4.desc2
的值如何使用UNION
将两个查询组合到一个查询中?
只是将联合放在查询之间。您必须别名列
执行此操作:
Query1 UNION Query2; -- for union without duplicates (the set union as in maths)
或
Query1 UNION ALL Query2; -- for union with duplicates
所以应该是:
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'
AND b.type = 'Type1'
UNION
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'
AND b.type = 'Type2';