背景:两个表workacacdata.movementdata.
movementdata中为日期提供过滤,及db_id提供过滤(此表中db_id是主键,唯一)
workacacdata中存在三种数据(此表中db_id不唯一,引用自上表)
A类型
recordkind的顺序为
A101 ->其他->A110 或者 A101->其他(不包括A110)
B类型 A110->A101->其他->A110
C类型 A110->A101->其他
数据的db_id相同代表数据属于同一组,每一组数据只属于一种类型A或B或C
相同db_id的一组数据 用record_id来表示它插入的顺序
所以
对A类数据来说
record_kind=A101的数据的record_id肯定小于和它同组的record_kind=A110的数据的record_id
以此类推
B类型数据:必存在两个record_kind=A110 的和一个record_kind=A101的数据,且 record_kind=A101的数据的record_id在三者record_id中排序居中
C........
要求:
取出所有A类型数据的开始日期
delivery_start_date
看起来超复杂,感觉做了很多次的重复查询.
于是修改之:
结果却不尽如人意,修改后的语句怎么看执行的查询数也不多啊,可用SQLServer自带的性能分析工具一测,优化后的反而更慢.
尤其在日期条件发生变化后.
修改后的语句read指数明显减少,但cpu指数明显增加
难道是我测试的表不够大???
workacacdata表1400条
movementdata表80条
原sql语句:
select distinct( convert(char(10),delivery_start_date,112) ) as selectdate
from movementdata
where (
delivery_start_date >= '2008-04-24 00:00:00' and
delivery_start_date < '2008-07-26 00:00:00') and
(
(
--选出A和B类型
(db_id in
(select db_id from
(
select
a.db_id,
count(a.record_kind) as cnt
from workacacdata a, workacacdata b,movementdata c
where
(upper(a.record_kind)='A110') and
(upper(b.record_kind)='A201') and
a.db_id=c.db_id and
c.delivery_start_date >= '2008-04-24 00:00:00' and
c.delivery_start_date < '2008-07-26 00:00:00' and
a.db_id=b.db_id
group by a.db_id
)
deliverytbl
where (cnt=1)
)
) and
--选出A存在A110数据
(db_id in
(
select a.db_id as db_id
from workacacdata a, workacacdata b,movementdata c
where
upper(a.record_kind)='A101' and
upper(b.record_kind) = 'A110' and
a.db_id=c.db_id and
c.delivery_start_date >= '2008-04-24 00:00:00'and
c.delivery_start_date < '2008-07-26 00:00:00' and
a.db_id = b.db_id and
a.record_id<b.record_id
)
)
)
--选出不存在A110的A类型数据
or (
db_id in
(
select distinct(db_id)
from movementdata
where
delivery_start_date >= '2008-04-24 00:00:00' and
delivery_start_date < '2008-07-26 00:00:00' and
db_id not in
(
select distinct(a.db_id)
from workacacdata a,movementdata b
where
a.db_id=b.db_id and
b.delivery_start_date >= '2008-04-24 00:00:00' and
b.delivery_start_date < '2008-07-26 00:00:00' and
upper(a.record_kind)='A110'
)
)
)
)
order by selectdate
修改后的语句:
--选出所有不同的db_id,包括A.B.C
select distinct( convert(char(10),delivery_start_date,112) )
from workacacdata a,movementdata b
where
b.delivery_start_date >= '"+start_time+"' and
b.delivery_start_date < '"+end_time+"' and
upper(a.record_kind)='A101' and
a.db_id=b.db_id and
--排除B和C类型的
a.db_id not in(
--选出B和C类型的
select a.db_id as db_id
from workacacdata a,movementdata b ,workacacdata c
where
b.delivery_start_date >= '"+start_time+"' and
b.delivery_start_date < '"+end_time+"' and
upper(a.record_kind)='A101' and
a.db_id=b.db_id and
upper(c.record_kind) = 'A110' and
c.db_id=a.db_id and
c.record_id<a.record_id
)