两个不同的查询作为一个结果在 oracle SQL 中的输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20636192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
output of two different queries as one result in oracle SQL
提问by Himshu1710
I have two different table on which i apply select query with some filters and aggregate functions like SUM,COUNT,SUBSTR.
我有两个不同的表，我在其中应用带有一些过滤器和聚合函数（如 SUM、COUNT、SUBSTR）的选择查询。
I want to get these two different output in a single result.example:
我想在单个 result.example 中获得这两个不同的输出：
Query 1:
查询 1：
SELECT
a.message_type,
a.queue_seqnum,
b.queue_seqnum,
SUBSTR(b.char_data,1,2) files
FROM
ad_in_messageheader a,
ad_in_messagedetail b
WHERE
a.queue_seqnum = b.queue_seqnum AND
a.MESSAGE_TYPE IN ('ERP_COSTS_SMRY','ERP_SALES_SMRY','ERP_SPEND_SMRY') AND
a.create_time > '17-DEC-13 07.00.00 AM'
ORDER BY
a.queue_seqnum desc;
Query 2:
查询 2：
SELECT
a.message_type,
count(a.message_type) count
FROM
ad_in_messageheader a
WHERE
a.MESSAGE_TYPE in ('ERP_COSTS','ERP_SALES','ERP_SPEND') AND
create_time > '17-DEC-13 07.00.00 AM'
GROUP BY
a.message_type;
I have tried UNIONand UNION ALLboth. But those are not working. I also tried to Select * from (query 1),(query 2), But it also did not work. Kindly suggest me some solution which will be helpful in this scenario. Thanks.
我曾尝试UNION和UNION ALL两者。但这些都行不通。我也试过Select * from (query 1),(query 2)，但是也没用。请向我建议一些在这种情况下会有所帮助的解决方案。谢谢。
回答by Olivier Jacot-Descombes
There are two ways of putting queries together: Sideways by using joins and on top of each other with unions. When using joins the result will include columns of both queries. When using unions, the result will include rows of both queries. For unions to work, both queries must return the same number of corresponding columns.
有两种方法可以将查询放在一起：使用连接的横向和使用联合在彼此之上。使用连接时，结果将包括两个查询的列。使用联合时，结果将包括两个查询的行。要使联合工作，两个查询必须返回相同数量的相应列。
I assume that you want to add the count calculated in the second query as column to the first query. This works like this (I'm using the new JOINsyntax):
我假设您想将在第二个查询中计算的计数作为列添加到第一个查询中。这就像这样（我使用的是新JOIN语法）：
SELECT
q1.x, q1.y, q2.z, ...
FROM
(SELECT ... FROM ...) q1
LEFT JOIN
(SELECT ... FROM ...) q2
ON q1.column = q2.column
You can also use INNER JOINinstead of LEFT JOINif you know that query2 yields at least one row for each row of query1 or if you are not interested in rows from query1 where corresponding rows are missing from query2.
如果您知道 query2 为 query1 的每一行生成至少一行，或者如果您对 query1 中的行不感兴趣，而 query2 中缺少相应的行INNER JOIN，LEFT JOIN则也可以使用代替。
SELECT
q1.message_type,
q1.queue_seqnum,
q1.files,
q2.message_count
FROM (SELECT
a.message_type,
a.queue_seqnum,
SUBSTR(b.char_data, 1, 2) files
FROM
ad_in_messageheader a,
INNER JOIN ad_in_messagedetail b
ON a.queue_seqnum = b.queue_seqnum
WHERE
a.message_type IN ('ERP_COSTS_SMRY', 'ERP_SALES_SMRY', 'ERP_SPEND_SMRY') AND
a.create_time > '17-DEC-13 07.00.00 AM') q1
LEFT JOIN
(SELECT
a.message_type,
COUNT(a.message_type) message_count
FROM
ad_in_messageheader a
WHERE
a.message_type IN ('ERP_COSTS', 'ERP_SALES', 'ERP_SPEND') AND
create_time > '17-DEC-13 07.00.00 AM'
GROUP BY
a.message_type) q2
ON q1.message_type = q2.message_type
ORDER BY
q1.queue_seqnum DESC;
I would also do the sorting after joining the two sub queries, because the joining process could destroy any order established before.
我也会在加入两个子查询后进行排序，因为加入过程可能会破坏之前建立的任何顺序。
There is also a problem with the message types: You are not selecting the same message types in the two sub queries. In ORACLE, you can use the DECODEfunction to translate the message types to make them match
消息类型也存在问题：您没有在两个子查询中选择相同的消息类型。在 ORACLE 中，您可以使用该DECODE函数来翻译消息类型以使其匹配
In sub query 1:
在子查询 1 中：
SELECT
DECODE(a.message_type,
'ERP_COSTS_SMRY', 'ERP_COSTS',
'ERP_SALES_SMRY', 'ERP_SALES',
'ERP_SPEND_SMRY', 'ERP_SPEND') message_type
If create_timeis a DATEcolumn, you must convert the date/time string to a date.
如果create_time是DATE列，则必须将日期/时间字符串转换为日期。
WHERE
a.create_time > TO_DATE('17-12-2013 19:00:00', 'DD-MM-YYYY HH24:MI:SS')
(See https://stackoverflow.com/a/10178346/880990)
（见https://stackoverflow.com/a/10178346/880990）
Also use a four-digit year if possible. This is safer. Is 311931or 2031? Also, a month number will work also on systems with different locales. DECwould not be recognized on a German system. Instead DEZwould be expected.
如果可能，也使用四位数年份。这样更安全。是311931还是2031？此外，月份数字也适用于具有不同语言环境的系统。DEC在德国系统上不会被识别。反而DEZ会被期待。
回答by Haji
If you are using union or union all to combine multiple query, then each query should have same number of columns with same data types.
如果您使用 union 或 union all 来组合多个查询，那么每个查询应该具有相同数量的具有相同数据类型的列。
Use below query:
使用以下查询：
SELECT a.message_type,a.queue_seqnum, b.queue_seqnum,SUBSTR (b.char_data,1,2)
files,0 as count FROM ad_in_messageheader a, ad_in_messagedetail b WHERE a.queue_seqnum =
b.queue_seqnum AND a.MESSAGE_TYPE in
('ERP_COSTS_SMRY','ERP_SALES_SMRY','ERP_SPEND_SMRY') AND
a.create_time > '17-DEC-13 07.00.00 AM'
union all
SELECT a.message_type,'' as queue_seqnum,'' as queue_seqnum, '' as files
count(a.message_type) count FROM ad_in_messageheader a
where a.MESSAGE_TYPE in ('ERP_COSTS','ERP_SALES','ERP_SPEND') AND
create_time > '17-DEC-13 07.00.00 AM' group by a.message_type
And you have to use order byfinally after combine both queries..
并且您必须order by在结合两个查询后使用finally ..
