sql - 甲骨文 SQL : Optimizing LEFT OUTER JOIN of two similar select statements to be smaller and/or more efficient

Question

所以我有这个 Oracle SQL 查询:

SELECT man.Toilet_Type, NVL(man.manual_PORTA_POTTY, 0) MANUAL, NVL(reg.regular_PORTA_POTTY, 0) REGULAR FROM (
SELECT A.Visitor Toilet_Type, COUNT(A.Toilet_ID) MANUAL_PORTA_POTTY FROM 
    BORE.EnragedPotty A,
    BORE.SemiEnragedPotty B,
    BORE.ManualPotty C
WHERE B.SemiEnragedPotty_ID = C.SemiEnragedPotty_ID 
    AND B.Toilet_ID = A.Toilet_ID
GROUP BY Visitor
ORDER BY Visitor ASC) man

LEFT OUTER JOIN
    (SELECT A.Visitor Toilet_Type, COUNT(B.Toilet_ID) REGULAR_PORTA_POTTY FROM 
        BORE.EnragedPotty A,
        BORE.RegularPotty B
     WHERE B.Toilet_ID = A.Toilet_ID
     GROUP BY Visitor
     ORDER BY Visitor ASC) reg ON man.Toilet_Type = reg.Toilet_Type

这给出了两个表结果。第一个查询 man 给我以下输出:

+===============+========+
| Toilet_Type   | Manual |
+===============+========+
| Portable      |  234   |
+---------------+--------+
| Home          |  10    |
+---------------+--------+
| Assassination |  2     | 
+---------------+--------+

第二个查询 reg 给出了与上面相同的输出，但使用的是 REGULAR 而不是 MANUAL。

我想做的是以更有效的方式查询数据库。我希望输出的格式如下:

+===============+========+=========+
| Toilet_Type   | Manual | Regular |
+===============+========+=========+
| Portable      |  234   |    444  |
+---------------+--------+---------+
| Home          |  10    |    222  |
+---------------+--------+---------+
| Assassination |  2     |    111  | 
+---------------+--------+---------+

当然这可以在不使用 LEFT OUTER JOIN 的情况下在单个查询中完成吗？

Answer 1

这是未经测试的，因为我没有任何示例数据，但我认为与此类似的事情可能会在一个查询中完成:

  SELECT
    E.Visitor Toilet_Type,
    SUM(case when SE.SemiEnragedPotty_ID is not null and
        M.Toilet_ID is not null then 1 else 0 end) MANUAL_PORTA_POTTY,
    SUM(case when R.Toilet_ID is not null then 1 else 0 end) REGULAR_PORTA_POTTY
  FROM 
    BORE.EnragedPotty E,
    BORE.SemiEnragedPotty SE,
    BORE.ManualPotty M,
    BORE.RegularPotty R
  WHERE
    E.SemiEnragedPotty_ID = SE.SemiEnragedPotty_ID (+) AND
    E.Toilet_ID = M.Toilet_ID (+)
    E.Toilet_ID = R.Toilet_ID (+)
  GROUP BY Visitor
  ORDER BY Visitor ASC

我可能漏掉了一些细节——我不得不重命名你的别名以跟随哪个表，所以如果我放错了其中一个，我不会感到震惊。