从group by子句中避免列 [英] Avoiding a column from group by clause

问题描述

我有这个SP，我试图在Oracle目标表中插入记录（11.2）。这里的子查询命名为'a'，其中我有一个列PROJECT，它是子查询'b'的连接条件之一，但是如果我将列''project'放在group by子句中，这里没有得到所需的结果子查询'a'。但同时我需要引用子查询的加入条件之一'b'



我尝试过： < br $>

I have this SP, where am trying to insert records in an Oracle target table(11.2). Here in the subquery naming as 'a' where i have a column PROJECT which is one of the joining condition for the subquery 'b' but here am not getting the desired result if i put the column 'project' in the group by clause in subquery 'a'. But at same time i need to refer the same as one of the joining conditions of subquery 'b'

What I have tried:

create or replace PROCEDURE PROC1 AS 
BEGIN       
INSERT INTO TARGET TABLE
 SELECT
        a.SECT,
        a. REG,
        a.USRN,
        a.PRO_CT,
        a.CATG,
        a.TLT,
        a.SN_YR_MN,
        a.SN_YR,
        a.SN_MN,
        a.UNIQUE_ISSUES_CNT,
        a.FILES_SN_ISS_CT,
        b.TOT_FILES_SN_CT,
        b.TOT_SN_CT,
        CURRENT_DATE
       FROM(select SECT, REG,USRN,PROJECT,count(DISTINCT concat(PROJECT,SOLUTION)) PRO_CT,CATG,TLT,SN_YR_MN,SN_YR,SN_MN,
      count(DISTINCT ISSUE_ID) UNIQUE_ISSUES_CNT,count(DISTINCT PATH)FILES_SN_ISS_CT
      FROM table1
      GROUP BY SECT, REG,PROJECT,USRN,CATG,TLT,SN_YR_MN,SN_YR,SN_MN)a
      JOIN
      (
      SELECT    USRN,
                PROJECT,
                SECT,
                 REG,
                SN_YR_MN,
                SN_YR,
                SN_MN,
                count(DISTINCT RPT_SCAN_SUMMARY.PATH)  TOT_FILES_SN_CT,
                count(DISTINCT RPT_SCAN_SUMMARY.SCAN_ID) TOT_SN_CT
                FROM table2                
                GROUP BY USRN,
                      PROJECT,
                      SECT, 
                       REG,
                      SN_YR_MN,
                      SN_YR,
                      PROJECT,
                      SN_MN) b ON a.USRN = b.USRN AND a.SECT = b.SECT AND 
         a. REG = b. REG AND a.SN_YR_MN = b.SN_YR_MN  AND a.SN_YR= b.SN_YR AND a.SN_MN= b.SN_MN AND a.PROJECT=b.PROJECT;

推荐答案

GROUP BY USRN,
         PROJECT,   -- !!!
         SECT,
          REG,
         SN_YR_MN,
         SN_YR,
         PROJECT, --- !!!
         SN_MN) b

格式化您的查询更有助于发现这些事情。

"Formatting" you queries better helps to spot these things.

这篇关于从group by子句中避免列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！