select slstitl.tilcod from slstitl,
( (( select distinct T2.bastilcod tilcod from dtlsmtl T1,slsvolm T2
where T1.Tilcod=T2.Tilcod and T1.tilcod like '299%' and
cndkbn in('001','010') or cndkbn like '3%' and
loccod not in('072','081' ,'082','091','092','096','999') and
MNGSHU not in('122','123','124','125','230','299','321','399','501','502','601','602','701','702','711','712','713','799','999')
and (T1.MNGSHU in('101','102','103','104','115','111','112','113','114','121')))
union all (select distinct T1.Tilcod from DTLSMTL T1 where T1.TILCOD not like '299%' and CNDKBN='001' or CNDKBN ='010'
or CNDKBN like '3%' and LOCCOD not in('072','081','082','091','092','096','999') and
MNGSHU not in('122','123','124','125','230','299','321','399','501','502','601','602','701','702','711','712','713','799','999')
and (T1MNGSHU in('101','103','104','115','111','112','113','114','115','121','102',''))))
union (( select distinct T1.TILCOD from DTLODER T1 where CNDKBH='8%' and (T1.MNGSHU in('101','102','103','104',
'111','112','113','114', '115 ','121') ) )) )
tablea,( ( select B.TILCOD from (select DISTINCT TILCOD FROM schclas where tagdata in('726','77877','K778')) a,
( select DISTINCT Tilcod from slstitl where tilshu in('301','302','303','304','311','312','321','399','401','411','499')) B
where a.tilcod=b.tilcod and (a.tilcod in(select DISTINCT tilcod from DTlsmtl where cndkbn in ('001','010') or
cndkbn like '3%' and loccod not in ('072','081','082','091','092','096','999') and
MNGSHU not in('122','123','124','125','230','299','321','399','501','502','601','602','701','702','711','712','713','799','999'))
or a.tilcod in (select DISTINCT tilcod from dtloder where cndkbn='8%') ) ) ) tableb where slstitl.tilcod=tablea.tilcod and
slstitl.tilcod=tableb.tilcod
order by title_rd,volume_num_sf--创建聚合索引
CREATE UNIQUE CLUSTERED INDEX Idx1 ON slstitl(tilcod)
CREATE UNIQUE CLUSTERED INDEX Idx2 ON tableb(tilcod)
( (( select distinct T2.bastilcod tilcod from dtlsmtl T1,slsvolm T2
where T1.Tilcod=T2.Tilcod and T1.tilcod like '299%' and
cndkbn in('001','010') or cndkbn like '3%' and
loccod not in('072','081' ,'082','091','092','096','999') and
MNGSHU not in('122','123','124','125','230','299','321','399','501','502','601','602','701','702','711','712','713','799','999')
and (T1.MNGSHU in('101','102','103','104','115','111','112','113','114','121')))
union all (select distinct T1.Tilcod from DTLSMTL T1 where T1.TILCOD not like '299%' and CNDKBN='001' or CNDKBN ='010'
or CNDKBN like '3%' and LOCCOD not in('072','081','082','091','092','096','999') and
MNGSHU not in('122','123','124','125','230','299','321','399','501','502','601','602','701','702','711','712','713','799','999')
and (T1MNGSHU in('101','103','104','115','111','112','113','114','115','121','102',''))))
union (( select distinct T1.TILCOD from DTLODER T1 where CNDKBH='8%' and (T1.MNGSHU in('101','102','103','104',
'111','112','113','114', '115 ','121') ) )) )
tablea,( ( select B.TILCOD from (select DISTINCT TILCOD FROM schclas where tagdata in('726','77877','K778')) a,
( select DISTINCT Tilcod from slstitl where tilshu in('301','302','303','304','311','312','321','399','401','411','499')) B
where a.tilcod=b.tilcod and (a.tilcod in(select DISTINCT tilcod from DTlsmtl where cndkbn in ('001','010') or
cndkbn like '3%' and loccod not in ('072','081','082','091','092','096','999') and
MNGSHU not in('122','123','124','125','230','299','321','399','501','502','601','602','701','702','711','712','713','799','999'))
or a.tilcod in (select DISTINCT tilcod from dtloder where cndkbn='8%') ) ) ) tableb where slstitl.tilcod=tablea.tilcod and
slstitl.tilcod=tableb.tilcod
order by title_rd,volume_num_sf--创建聚合索引
CREATE UNIQUE CLUSTERED INDEX Idx1 ON slstitl(tilcod)
CREATE UNIQUE CLUSTERED INDEX Idx2 ON tableb(tilcod)
看看执行计划,利用上索引没有?
CREATE UNIQUE CLUSTERED INDEX Idx1 ON slstitl(tilcod)
CREATE UNIQUE CLUSTERED INDEX Idx2 ON tableb(tilcod)
这里
还有别的地方可以加?
select slstitl.tilcod
from slstitl,
(
---------->1
((select distinct T2.bastilcod tilcod
from dtlsmtl T1, slsvolm T2
where T1.Tilcod = T2.Tilcod
and T1.tilcod like '299%'
and cndkbn in ('001', '010')
or cndkbn like '3%'
and loccod not in
('072', '081', '082', '091', '092', '096', '999')
and MNGSHU not in
('122', '123', '124', '125', '230', '299', '321', '399',
'501', '502', '601', '602', '701', '702', '711', '712',
'713', '799', '999')
and (T1.MNGSHU in ('101', '102', '103', '104', '115', '111',
'112', '113', '114', '121'))) union all
(select distinct T1.Tilcod
from DTLSMTL T1
where T1.TILCOD not like '299%'
and CNDKBN = '001'
or CNDKBN = '010'
or CNDKBN like '3%'
and LOCCOD not in
('072', '081', '082', '091', '092', '096', '999')
and MNGSHU not in
('122', '123', '124', '125', '230', '299', '321', '399',
'501', '502', '601', '602', '701', '702', '711', '712',
'713', '799', '999')
and (T1MNGSHU in ('101', '103', '104', '115', '111', '112', '113',
'114', '115', '121', '102', ''))))
----------<1
union
---------->2
((select distinct T1.TILCOD
from DTLODER T1
where CNDKBH = '8%'
and (T1.MNGSHU in ('101', '102', '103', '104', '111', '112',
'113', '114', '115 ', '121'))))
----------<2
) tablea,
((
select B.TILCOD
from
----------<3
(select DISTINCT TILCOD
FROM schclas
where tagdata in ('726', '77877', 'K778')) a,
----------<3
---------->4
(select DISTINCT Tilcod
from slstitl
where tilshu in ('301', '302', '303', '304', '311', '312',
'321', '399', '401', '411', '499')) B
----------<4
where a.tilcod = b.tilcod
---------->5
and (a.tilcod in
(select DISTINCT tilcod
from DTlsmtl
where cndkbn in ('001', '010')
or cndkbn like '3%'
and loccod not in
('072', '081', '082', '091', '092', '096', '999')
and MNGSHU not in
('122', '123', '124', '125', '230', '299', '321',
'399', '501', '502', '601', '602', '701', '702',
'711', '712', '713', '799', '999')) or
a.tilcod in
(select DISTINCT tilcod from dtloder where cndkbn = '8%'))
----------<5
)) tableb
where slstitl.tilcod = tablea.tilcod
and slstitl.tilcod = tableb.tilcod
order by title_rd, volume_num_sf
----------<1
上面的一行:
===============================================================
T1MNGSHU in ('101', '103', '104', '115', '111', '112', '113',
'114', '115', '121', '102', '')
===============================================================
这句的‘T1MNGSHU’、in 之后的'',可能是搂主笔误!
但主要是in的语法作用完全屏蔽上一句not in,所以删除其中一个。
where slstitl.tilcod = tablea.tilcod
and slstitl.tilcod = tableb.tilcod这句slstitl.tilcod =tablea.tilcod 就没有必要在创建tablea的时候中间union all,可用union即可,所以可以把tablea简化一些写成:
(
---------->1
select distinct T2.bastilcod tilcod
from dtlsmtl T1, slsvolm T2
where T1.Tilcod = T2.Tilcod
and T1.tilcod like '299%'
and cndkbn in ('001', '010')
or cndkbn like '3%'
and loccod not in
('072', '081', '082', '091', '092', '096', '999')
and MNGSHU not in
('122', '123', '124', '125', '230', '299', '321', '399',
'501', '502', '601', '602', '701', '702', '711', '712',
'713', '799', '999')
and T1.MNGSHU in ('101', '102', '103', '104', '115', '111',
'112', '113', '114', '121')
union
select distinct T1.Tilcod
from DTLSMTL T1
where T1.TILCOD not like '299%'
and CNDKBN = '001'
or CNDKBN = '010'
or CNDKBN like '3%'
and LOCCOD not in
('072', '081', '082', '091', '092', '096', '999')
/* and MNGSHU not in
('122', '123', '124', '125', '230', '299', '321', '399',
'501', '502', '601', '602', '701', '702', '711', '712',
'713', '799', '999')*/
and T1.MNGSHU in ('101', '103', '104', '115', '111', '112', '113',
'114', '115', '121', '102')
----------<1
union
---------->2
select distinct T1.TILCOD
from DTLODER T1
where CNDKBH = '8%'
and T1.MNGSHU in ('101', '102', '103', '104', '111', '112',
'113', '114', '115 ', '121')
----------<2
) tablea
你这语句是有些那个,你这个sql不能运行,其中有一句写错了,先按照顺序美化后的sql如下:美化。
好 我从来没有美化的习惯。
今天总算学到一招了
---------->5
and (a.tilcod in
.....
(select DISTINCT tilcod from dtloder where cndkbn = '8%'))
----------<5
完全是子表a的条件,建议完全写到子表a中,这样子表a,b关联的条理就非常清晰了。第四,建议你把下面的2个逻辑相同的语句统一一种写法:
=============================
and cndkbn in ('001', '010')and CNDKBN = '001'
or CNDKBN = '010'
=============================
而且如果有and、or同时存在的时候一定要在or的逻辑两边加括号,如:
and (CNDKBN = '001'or CNDKBN = '010')