在Oracle SQL中将两个不同查询的输出作为一个结果 [英] output of two different queries as one result in oracle SQL

问题描述

我有两个不同的表，我在这些表上应用带有一些过滤器和汇总函数(例如SUM，COUNT，SUBSTR)的选择查询.

I have two different table on which i apply select query with some filters and aggregate functions like SUM,COUNT,SUBSTR.

我想在一个结果中获得这两个不同的输出.例如:

I want to get these two different output in a single result.example:

查询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;

查询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;

我都尝试过UNION和UNION ALL.但是那些没有用.我也尝试了Select * from (query 1),(query 2)，但是它也没有用.请给我建议一些解决方案，在这种情况下会有所帮助.谢谢.

I have tried UNION and UNION ALL both. 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.

推荐答案

有两种将查询放在一起的方法:侧向使用联接，并通过联合彼此叠加.使用联接时，结果将包括两个查询的列.使用联合时，结果将包括两个查询的行.为了使联合工作，两个查询必须返回相同数量的相应列.

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.

我假设您想将在第二个查询中计算出的计数作为列添加到第一个查询中.就像这样(我正在使用新的JOIN语法):

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 JOIN syntax):

SELECT
    q1.x, q1.y, q2.z, ...
FROM
    (SELECT ... FROM ...) q1
    LEFT JOIN
    (SELECT ... FROM ...) q2
        ON q1.column = q2.column

如果您知道query2为query1的每一行至少产生一行，或者如果您对query1的行不感兴趣，而query2中缺少相应的行，则也可以使用INNER JOIN代替LEFT JOIN.

You can also use INNER JOIN instead of LEFT JOIN if 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.

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.

消息类型也存在问题:您没有在两个子查询中选择相同的消息类型.在ORACLE中，可以使用DECODE函数转换消息类型以使其匹配

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 DECODE function to translate the message types to make them match

在子查询1中:

SELECT
    DECODE(a.message_type,
           'ERP_COSTS_SMRY', 'ERP_COSTS', 
           'ERP_SALES_SMRY', 'ERP_SALES',
           'ERP_SPEND_SMRY', 'ERP_SPEND') message_type

---

如果create_time是DATE列，则必须将日期/时间字符串转换为日期.

---

If create_time is a DATE column, you must convert the date/time string to a date.

WHERE
    a.create_time > TO_DATE('17-12-2013 19:00:00', 'DD-MM-YYYY HH24:MI:SS')

(请参阅 https://stackoverflow.com/a/10178346/880990 )

如果可能，还请使用四位数的年份.这样比较安全.是31 1931还是2031?同样，一个月数也将在具有不同区域设置的系统上工作. DEC在德语系统中不会被识别.取而代之的是DEZ.

Also use a four-digit year if possible. This is safer. Is 31 1931 or 2031? Also, a month number will work also on systems with different locales. DEC would not be recognized on a German system. Instead DEZ would be expected.

这篇关于在Oracle SQL中将两个不同查询的输出作为一个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！