sql - 忽略选择查询中的列但需要用作连接条件
问题描述
在这里我有这个过程,我需要将数据插入目标表 4.在所有这些子查询中,我需要根据 scan_id 加入,但同时我不应该在选择查询中使用 scan_id 分组结果集合被分割成更多的粒度。即使我尝试使用“按子句分区”,它也提供了更多的记录。我们如何避免 scan_id 列,应该使用与连接条件相同的列。目标表 4
SOA_SECTOR VARCHAR2(100 CHAR)
SOA_REGION VARCHAR2(100 CHAR)
PROJECT NOT NULL VARCHAR2(256 CHAR)
SOLUTION VARCHAR2(256 CHAR)
FIRST_SCAN_ISSUE_CNT NOT NULL NUMBER(10)
FIRST_FILES_WITH_ISSUES_CNT NOT NULL NUMBER(10)
FIRST_SCAN_FILE_CNT NOT NULL NUMBER(10)
TOTAL_FILES_FOR_PROJECT_CNT NOT NULL NUMBER(10)
LAST_SCAN_ISSUE_CNT NOT NULL NUMBER(10)
LAST_FILES_WITH_ISSUES_CNT NOT NULL NUMBER(10)
LAST_SCAN_FILE_CNT NOT NULL NUMBER(10)
PRIOR_SCAN_ISSUE_CNT NOT NULL NUMBER(10)
PRIOR_FILE_WITH_ISSUES_CNT NOT NULL NUMBER(10)
PRIOR_SCAN_FILE_CNT NOT NULL NUMBER(10)
LOAD_DATE_TIME NOT NULL DATE
过程
CREATE OR REPLACE PROCEDURE PROC AS
BEGIN
INSERT INTO table4
SELECT first_scan_issues.SOA_SECTOR,
first_scan_issues.SOA_REGION,
a.PROJECT,
a.SOLUTION,
first_scan_issues.CNT1,
first_scan_issues.CNT2,
file_scans.CNT7,
project_files_count.COUNT,
last_scan_issues.CNT3,
last_scan_issues.CNT4,
file_scans.CNT8,
prior_scan_issues.CNT5,
prior_scan_issues.CNT6,
file_scans.CNT9,
CURRENT_DATE
FROM( select PROJECT,SOLUTION,FIRST_SCAN_ID,LAST_SCAN_ID,PRIOR_SCAN_ID from table1 order by project
)a
LEFT OUTER JOIN
(
select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,
COUNT(DISTINCT ISSUE_ID) CNT3,
COUNT(DISTINCT FILE_ID) CNT4
from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID
) first_scan_issues on first_scan_issues.SCAN_ID = a.FIRST_SCAN_ID AND first_scan_issues.PROJECT =a.PROJECT
LEFT OUTER JOIN
(
select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,
COUNT(DISTINCT ISSUE_ID) CNT3,
COUNT(DISTINCT FILE_ID) CNT4
from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID
)last_scan_issues ON last_scan_issues.SCAN_ID = a.LAST_SCAN_ID AND last_scan_issues.PROJECT =a.PROJECT
LEFT OUTER JOIN
(
select SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID,
COUNT(DISTINCT ISSUE_ID) CNT3,
COUNT(DISTINCT FILE_ID) CNT4
from table2 group by SOA_SECTOR,SOA_REGION,PROJECT,SOLUTION,SCAN_ID
) prior_scan_issues ON PRIOR_scan_issues.SCAN_ID = a.PRIOR_SCAN_ID AND prior_scan_issues.PROJECT =a.PROJECT
LEFT OUTER JOIN
(
select project,solution, count(distinct FIRST_SCAN_ID) CNT7,
count(distinct LAST_SCAN_ID) CNT8,count(distinct PRIOR_SCAN_ID) CNT9
from table1 group by project,solution order by project
)file_scans ON file_scans.PROJECT=a.PROJECT
JOIN
(
SELECT PROJECT,SOLUTION,SOA_SECTOR,SOA_REGION,
COUNT(DISTINCT PATH) COUNT
FROM table3
GROUP BY PROJECT,SOLUTION,SOA_SECTOR,SOA_REGION
) project_files_count
ON project_files_count.PROJECT = a.PROJECT;
END;
CREATE TABLE table1 (
PROJECT VARCHAR2(256 CHAR) NOT NULL,
SOLUTION VARCHAR2(256 CHAR),
FIRST_SCAN_ID NUMBER(10) NOT NULL,
LAST_SCAN_ID NUMBER(10) NOT NULL,
PRIOR_SCAN_ID NUMBER(10) NOT NULL,
CONSTRAINT pk_tmp_first_last_scan_ids_id PRIMARY KEY (PROJECT)
);
CREATE TABLE table2
(
ISSUE_ID NUMBER(10)
CONSTRAINT RPT_ISSUE_SUMMARY_PK
PRIMARY KEY,
SOA_SECTOR VARCHAR2(128),
SOA_REGION VARCHAR2(128),
USERNAME VARCHAR2(128),
PROJECT VARCHAR2(256),
SOLUTION VARCHAR2(256),
PATH VARCHAR2(2048),
TIME DATE,
CATEGORY VARCHAR2(512),
TITLE VARCHAR2(512),
ISSUE_IMP VARCHAR2(256),
CLICKED NUMBER(3),
SCAN_ID NUMBER(10),
SESSION_ID NUMBER(10),
RULEPACK_RULE_ID NUMBER(10),
FILE_ID NUMBER(10),
FILE_INFO_ID NUMBER(10),
SCAN_YEAR_MON VARCHAR2(7),
SCAN_YEAR VARCHAR2(4),
SCAN_MONTH VARCHAR2(2),
LOAD_TIME DATE NOT NULL
);
CREATE TABLE table3
(
SOA_SECTOR VARCHAR2(128),
SOA_REGION VARCHAR2(128),
USERNAME VARCHAR2(128),
PROJECT VARCHAR2(256) NOT NULL,
SOLUTION VARCHAR2(256),
PATH VARCHAR2(2048) NOT NULL,
TIME DATE NOT NULL,
LOC NUMBER(10) NOT NULL,
SCAN_ID VARCHAR2(256),
SESSION_ID VARCHAR2(256),
FILE_ID VARCHAR2(256),
FILE_INFO_ID VARCHAR2(256),
SCAN_YEAR_MON VARCHAR2(7),
SCAN_YEAR VARCHAR2(7),
SCAN_MONTH VARCHAR2(7),
LOAD_DATE_TIME DATE NOT NULL,
CONSTRAINT rpt_scan_summary_pk PRIMARY KEY (SCAN_ID, FILE_ID)
)
解决方案
推荐阅读
- python - 如何在 swig 中使用 numpy.i?
- chisel - 如何制作数据结构
- node.js - 在哪里可以找到 Google API 客户端的 credentials.json?
- python - 如何从 URL 中提取文件名以及附加的一些额外数据?
- c# - 如何使用使用哈希加密、C#、ASP.NET MVC、实体框架的存储过程验证帐户
- rsa - 如何使用我在 PuTTY 中生成的 SSH-1 RSA 公钥/私钥对
- android - 如何在 android webview 中添加打印支持
- javascript - 如何使用 javascript 循环和数组显示图像幻灯片?
- go - 将 libstdc++-6.dll 解码为 libstdc++-6.dll 的最快方法
- r - 你如何在 R 中创建一个新的字符变量?