如何优化和组合两个SQL查询

Question

我有两个SQL查询。首先是这些pl - 'IQ'，'SM'，'T2‘，其次是pl ='AT'，'VV'，'CM’。我收到以下资料-

​

​

有没有优化这两个查询并将它们合并为一个查询的选项？CASE语句中的信息是可重复的，只是pl不同。

第一个查询：

/* Formatted on 10.7.2016 1:23:06  (QP5 v5.163.1008.3004) */
SELECT DISTINCT
       pl,
       SUM (
          CASE
             WHEN pl IN ('IQ', 'SM', 'T2')
             THEN
                CASE
                   WHEN time LIKE '201601%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.27131)
                   WHEN time LIKE '201602%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.27609)
                   WHEN time LIKE '201603%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.263019)
                   WHEN time LIKE '201604%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.251591)
                   WHEN time LIKE '201605%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.248002)
                   WHEN time LIKE '201606%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.252972)
                   WHEN time LIKE '201607%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.248432)
                   WHEN time LIKE '201608%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.25790)
                   WHEN time LIKE '201609%'
                   THEN
                      ( (charge / POWER (10, decimals)) * 1.240378)
                   ELSE
                      0
                END
             ELSE
                0
          END)
       OVER ()
          AS charge
  FROM ch
 WHERE     TYPE = 'MO'
       AND pl IN ('IQ', 'SM', 'T2')
       AND time BETWEEN '20160101000000' AND '20160930235959'

第二个查询：

/* Formatted on 10.7.2016 1:36:11  (QP5 v5.163.1008.3004) */
SELECT DISTINCT
   pl,
   SUM (
      CASE
         WHEN pl IN ('AT', 'VV', 'CM')
         THEN
            CASE
               WHEN time LIKE '201601%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.27131)
               WHEN time LIKE '201602%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.27609)
               WHEN time LIKE '201603%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.263019)
               WHEN time LIKE '201604%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.251591)
               WHEN time LIKE '201605%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.248002)
               WHEN time LIKE '201606%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.252972)
               WHEN time LIKE '201607%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.248432)
               WHEN time LIKE '201608%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.25790)
               WHEN time LIKE '201609%'
               THEN
                  ( (charge / POWER (10, decimals)) * 1.240378)
               ELSE
                  0
            END
         ELSE
            0
      END)
   OVER ()
      AS charge
FROM ch
WHERE     TYPE = 'MO'
   AND pl IN ('AT', 'VV', 'CM')
   AND time BETWEEN '20160101000000' AND '20160930235959'

提前感谢您的帮助

Answer 1

你似乎想要union all

with q1 as (
      <first query here>
     ),
     q2 as (
      <second query here>
     )
select q1.*
from q1
union all
select q2.*
from q2;

Answer 2

您和stick在两个查询之间使用UNION将它们组合在一起。为了帮助你优化，我需要更多的信息，例如表结构，indexes...etc。

Answer 3

如果您的查询在select语句中具有相同的字段名称，则可以使用"UNION“。很简单，例如is，

select id,name from table1 where condition
Union
select id,name from table2 where condition

这将为您提供两个查询的组合结果。