从group by子句中避免列

本文介绍了从group by子句中避免列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.