Oracle

從子查詢中選擇多個列 - 連接方式

  • May 16, 2016

我有一個管理文件夾結構的表。因此，它具有父子關係。我們觸發一個查詢來匯總完整層次結構中的所有文件：

  SELECT  folder_name name,
     document_count,
     (SELECT SUM(e2.document_count) 
       FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
     )
     sum_sal
   FROM
     folder_mapping e1

現在我需要計算一列的總和

   SELECT  folder_name name,
     document_count,
     (SELECT SUM(e2.document_count), **sum(e2.folder_count)**

       FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
     )
     sum_sal
   FROM
     folder_mapping e1

此查詢拋出錯誤：> ORA-00913：值太多。

但是可以做些什麼來達到同樣的效果呢？

帶數據的數據庫結構：

在此處輸入圖像描述

預期輸出：

在此處輸入圖像描述

在 JSapkota 的幫助下查詢

SELECT
 fm.folder_mapping_id,
 fm_main.folder_name,
 fm.Total_Doc_Count,
 fm.Total_Folder_Count,
 --  fm.shared_flag
 DECODE(fm.shared_flag, fm_main.shared_flag
 ||'', (DECODE(fm.shared_flag, '0', 'Not Shared', '1', 'Fully Shared',
 'Partially Shared')), 'Partially Shared') shared_flag
FROM
 (
   SELECT
     folder_mapping_id,
     SUM(document_count) Total_Doc_Count,
     SUM(folder_count) Total_Folder_Count,
     SUM(SHARED_FLAG) SHARED_FLAG,
     SUM(attachment_flag) attachment_flag
   FROM
     (
       SELECT
         CONNECT_BY_ROOT folder_mapping_id AS folder_mapping_id,
         document_count,
         folder_count,
         SHARED_FLAG,
         attachment_flag
       FROM
         (
           SELECT
             *
           FROM
             folder_mapping
           WHERE
             organization_id='Org_498'
         )
         --where organization_id='Org_498'
         CONNECT BY PRIOR folder_mapping_id = parent_folder_mapping_id
     )
   GROUP BY
     folder_mapping_id
 )
 fm,
 (
   SELECT
     folder_mapping_id,
     document_count,
     folder_name,
     folder_count,
     SHARED_FLAG
   FROM
     folder_mapping
   WHERE
     organization_id='Org_498'
 )
 fm_main
WHERE
 fm_main.folder_mapping_id = fm.folder_mapping_id
ORDER BY
 fm.folder_mapping_id;

輸入如下： 在此處輸入圖像描述

目前輸出為： 在此處輸入圖像描述

輸出幾乎沒問題，除了我們希望root9顯示為完全共享，因為它是完全共享的子文件夾root8。

共享標誌遵循的

約定 –0 - 未共享 –1

  • 完全共享

–2 - 部分共享

如果一個文件夾被完全共享，它的子文件夾也被完全共享。

如果文件夾部分共享或完全共享，則其父文件夾部分共享。

插入查詢：

Insert into     FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (1,'root1','111',0,2,3,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (2,'root2','111',1,2,33,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (3,'root3','111',1,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (4,'root4','111',2,2,2,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (5,'root5','111',3,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (6,'root6','111',5,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (7,'root7','111',6,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (8,'root8','111',7,2,1,'1','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (9,'root9','111',8,2,1,'0','1','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');

ORA-00913 值過多

原因： SQL 語句需要兩組數量相等的值。當第二組包含的項目多於第一組時，會發生此錯誤。例如，WHERE 或 HAVING 子句中的子查詢可能返回太多列，或者 VALUES 或 SELECT 子句返回的列可能比 INSERT 中列出的更多。

**行動：**檢查每組中的項目數並更改 SQL 語句以使它們相等。

在您的情況下，您的子查詢將兩列返回到需要一列的第一個查詢。您可以按如下方式更改您的查詢。

SELECT  folder_name name,
 document_count,
 (SELECT SUM(e2.document_count)
   FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
 )
 sum_sal, (SELECT SUM(e2.document_count)
   FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
 ) folder_count_total
FROM
 folder_mapping e1

那隻是解決您的錯誤的方法。實際上，您可以使用以下查詢來獲得比原始查詢更好（在成本方面）的所需輸出。

SQL> desc folder_mapping;
Name                      Null?    Type
----------------------------------------- -------- ----------------------------
FOLDER_MAPPING_ID                  NUMBER
FOLDER_NAME                        VARCHAR2(20)
FOLDER_FILENET_ID                  NUMBER
PARENT_FOLDER_MAPPING_ID               NUMBER
FOLDER_COUNT                       NUMBER
DOCUMENT_COUNT                     NUMBER
SHARED_FLAG                        NUMBER
ATTACHMENT_FLAG                    NUMBER

SQL> select * from folder_mapping;

FOLDER_MAPPING_ID FOLDER_NAME          FOLDER_FILENET_ID
----------------- -------------------- -----------------
PARENT_FOLDER_MAPPING_ID FOLDER_COUNT DOCUMENT_COUNT SHARED_FLAG ATTACHMENT_FLAG
------------------------ ------------ -------------- ----------- ---------------
       4 root3                  111
              2        2          2           0           0

       1 root                    11
              0        2          3           0           0

       2 root1                  111
              1        2         33           0           0



SQL> SELECT folder_name, SUM(document_count) "Total_Doc_Count", SUM(folder_count) "Total_Folder_Count"  FROM(
  SELECT CONNECT_BY_ROOT folder_name as folder_name, document_count, folder_count
     FROM folder_mapping
     CONNECT BY PRIOR folder_mapping_id = parent_folder_mapping_id)
     GROUP BY folder_name;   

FOLDER_NAME      Total_Doc_Count Total_Folder_Count
-------------------- --------------- ------------------
root1                 35              4
root                  38              6
root3                  2              2

SQL>

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

相關問答

Oracle

將每行的連接限制為前 1 行

  • December 10, 2020
檢視問答
Oracle

如何從連接中獲取選擇，其中一個表中沒有結果

  • September 2, 2018
檢視問答
Oracle

如何計算屬性在內部查詢中出現的次數？

  • October 18, 2017
檢視問答
Oracle

oracle如何從兩個表中獲取分層數據？

  • September 15, 2016
檢視問答
Oracle

幫助加入 3 個表 Oracle XE 11g

  • April 15, 2016
檢視問答
Oracle

查詢優化OracleDBM小號○r一個Cl和D乙米小號Oracle DBMS

  • July 17, 2022
檢視問答