摘要統計資訊：多個聯接和分組依據

我有一個人行道檢查系統：

SIDEWALK_INSP

  INSP_ID
----------
        1
        2
        3

SIDEWALK_DEFICIENCY

   DEF_ID      INSP_ID   DEF_LENGTH
----------   ----------   ----------
       10            1           .5
       11            1            1
       12            1          1.5
       13            2            2
       14            2          2.5

SIDEWALK_OBSERVATION    

   OBS_ID      INSP_ID   OBS_LENGTH   OBS_TYPE
----------   ----------   ----------   --------
      100            2            5      a    
      101            2            6      b    
      102            2            7      b    
      103            2            8      b    
      104            3            9      a    
      105            3           10      a    
      106            3           11      b    

---

create table sidewalk_insp (insp_id number(10));
insert into sidewalk_insp values (1);
insert into sidewalk_insp values (2);
insert into sidewalk_insp values (3);
commit;

create table sidewalk_deficiency (
   def_id number(10), 
   insp_id number(10),
   def_length number(10,2));
insert into sidewalk_deficiency values (10,1,.5);
insert into sidewalk_deficiency values (11,1,1);
insert into sidewalk_deficiency values (12,1,1.5);
insert into sidewalk_deficiency values (13,2,2);
insert into sidewalk_deficiency values (14,2,2.5);
commit;

create table sidewalk_observation (
   obs_id number(10), 
   insp_id number(10),
   obs_length number(10,2),
   obs_type varchar(5));
insert into sidewalk_observation values (100,2,5,'a');
insert into sidewalk_observation values (101,2,6,'b');
insert into sidewalk_observation values (102,2,7,'b');
insert into sidewalk_observation values (103,2,8,'b');
insert into sidewalk_observation values (104,3,9,'a');
insert into sidewalk_observation values (105,3,10,'a');
insert into sidewalk_observation values (106,3,11,'b');
commit;

---

我想在單個查詢中計算以下內容：

• 缺陷長度（每次檢查）
• 觀察的長度，其中observation type = 'a'（每次檢查）
• 觀察的長度，無論類型如何（每次檢查）

輸出將如下所示：

+---------+------------+-------------------+----------------+
| INSP_ID | DEF_LENGTH | OBS_TYPE_A_LENGTH | OBS_ALL_LENGTH |
+---------+------------+-------------------+----------------+
|       1 |          3 |                   |                |
|       2 |        4.5 |                 5 |             26 |
|       3 |            |                19 |             30 |
+---------+------------+-------------------+----------------+

如何創建此查詢？

幹得好：

select
 si.insp_id, sd.def_length, so.obs_type_a_length, so.obs_all_length
from
 sidewalk_insp si
 left join 
   (select insp_id, sum(def_length) as def_length from sidewalk_deficiency group by insp_id) sd
   on (si.insp_id = sd.insp_id)
 left join 
   (select insp_id, sum(case when obs_type = 'a' then obs_length else 0 end) as obs_type_a_length,
           sum(obs_length) as obs_all_length from sidewalk_observation group by insp_id) so
   on (si.insp_id = so.insp_id)
order by 
 si.insp_id
;

  INSP_ID DEF_LENGTH OBS_TYPE_A_LENGTH OBS_ALL_LENGTH
---------- ---------- ----------------- --------------
        1          3                                 
        2        4.5                 5             26
        3                           19             30

引用自：https://dba.stackexchange.com/questions/197306