现有系统表和问题单两张表如下,系统表中有11个专业,而问题单中的每条记录对应一个专业,现在需要针对问题单统计出每个专业有多少条记录。
表一:系统表
create table MSS.MSS_SYSTEM
( MS_ID NUMBER(16) not null,
MS_CODE VARCHAR2(50),
MS_NAME VARCHAR2(100),
MM_ID NUMBER(16),
IS_YWLTJ NUMBER(1) default 0,
IMP_MODE NUMBER(1) default 2,
ITSM_NAME VARCHAR2(100),
MS_COMPANY VARCHAR2(10),
MS_CENTRE NUMBER(2),
MAP_MS_ID NUMBER(16),
MS_ORDER_ID NUMBER(3)
)
表二:问题单
create table MSS.MSS_QUESTION_RECORD
( MPS_ID NUMBER not null,
MPS_CODE VARCHAR2(100),
MPS_TOPIC VARCHAR2(100),
MPS_TITLE VARCHAR2(1000),
MPS_MAJOR VARCHAR2(50),
MPS_RELATE_SYS VARCHAR2(50),
MPS_AREA NUMBER,
MPS_QUE_DESC VARCHAR2(4000),
MPS_QUE_CAUSE VARCHAR2(3000),
MPS_METHOD VARCHAR2(3000),
MPS_SYS_BAR_CODE VARCHAR2(200),
MPS_AUDIT_STATUS NUMBER,
MPS_CURRENT_LINK NUMBER,
MPS_LINK_TIME DATE,
MPS_CURRENT_PERSON VARCHAR2(200),
MPS_BILL_FOUND_TIME DATE,
MPS_CLAIM_SOLVE_TIME1 NUMBER,
MPS_SURRORT_CENTRE VARCHAR2(100),
ITSM_ID NUMBER(16),
IS_OVERTIME NUMBER,
ETL_TIME DATE,
IS_COMPLETE_FLAG VARCHAR2(20),
MPS_CLAIM_SOLVE_TIME DATE,
SYSTEM_ID NUMBER(6) //对应系统表中的ms_id
)
统计各个专业对应的问题单记录数量如下:
select s.ms_name msName, count(distinct itsm_id) bTotalCnt
from Mss_Question_Record q, mss_system s
where s.is_ywltj = 1
and q.mps_surrort_centre = '2'
and q.system_id = s.ms_id
and q.mps_bill_found_time between
to_date('2016-09-05', 'yyyy-MM-dd') and
to_date('2016-09-11', 'yyyy-MM-dd') + 1
group by q.system_id, s.ms_name
msName
bTotalCnt
1
SAP-PS
26
2
SAP-MM
18
3
运维成本
3
4
法律辅助
1
5
审计系统
1
6
物流辅助
42
7
计划建设
53
8
外部门户
2
9
采购辅助
81
10
合同辅助
9
这里只统计出了10个专业的数量,而问题单中没有的那个专业并没有显示出来。
对应的改进SQL如下:
select s.ms_name msName, count(distinct itsm_id) bTotalCnt
from mss_system s
left join (select *
from Mss_Question_Record
where mps_surrort_centre = '2'
and mps_bill_found_time between
to_date('2016-09-05', 'yyyy-MM-dd') and
to_date('2016-09-11', 'yyyy-MM-dd') + 1) q
on q.system_id =s.ms_id
where s.is_ywltj = 1
group by q.system_id, s.ms_name
msName
bTotalCnt
1
SAP-PS
26
2
SAP-MM
18
3
运维成本
3
4
法律辅助
1
5
审计系统
1
6
物流辅助
42
7
计划建设
53
8
外部门户
2
9
采购辅助
81
10
合同辅助
9
11
ICT系统
0