我这个表有几百万条数据,包含删除的数据,现在就是要过虑掉插入后被删除过的数据,类型为INS01,DEL01,DEL01发生在INS01之后,根据相关条件过滤,请看如下语句,用的是not exists select * from (
select * from cdb.dbo.ERPContract E
where not exists
(
select * from
(
select R.id from
(
SELECT * FROM cdb.dbo.ERPContract
where CRMODE IN('DEL01')
)T
inner join cdb.dbo.ERPContract R
on T.CUSTOMER=R.CUSTOMER AND
T.STYLE=R.STYLE AND
T.KIND=R.KIND AND
T.COLOR=R.COLOR AND
R.CRMODE IN('INS01') AND R.ID<T.ID
)A where E.Id = A.id
)
)B
where B.id not in
(
SELECT C.id FROM cdb.dbo.ERPContract C
where CRMODE IN('DEL01')
)
先过滤插入后被删除的数据,再过滤删除数据,求优化
select * from cdb.dbo.ERPContract E
where not exists
(
select * from
(
select R.id from
(
SELECT * FROM cdb.dbo.ERPContract
where CRMODE IN('DEL01')
)T
inner join cdb.dbo.ERPContract R
on T.CUSTOMER=R.CUSTOMER AND
T.STYLE=R.STYLE AND
T.KIND=R.KIND AND
T.COLOR=R.COLOR AND
R.CRMODE IN('INS01') AND R.ID<T.ID
)A where E.Id = A.id
)
)B
where B.id not in
(
SELECT C.id FROM cdb.dbo.ERPContract C
where CRMODE IN('DEL01')
)
先过滤插入后被删除的数据,再过滤删除数据,求优化
解决方案 »
- 兄弟们谁给我看看这段代码怎么加啊,都写好了,就是不对,急,在线等10分钟,做完结帖
- |zyciis| 如何取出一条记录的ID号,同时更新他的操作状态Sel 而且在并发的时候不会出错重复读取 谢谢 第二贴
- select * from table,全为空字段不显示[在线等]
- 挑战一下你的数据库查询能力!
- 请教存储过程
- 请教一个用电量分时统计的问题
- 帮我看看这个sql,在sql server里没问题,但在access执行不了
- C++ 中怎么用DMO的连接数据库方法connect?
- 请问各位大虾,有关vb数据窗口列标题显示中文的问题!!
- 麻烦懂C#的大神帮忙看一下,datagridview怎么插入数据?
- openrowset 打开oracle 中 bolb字段 出错?
- 数据库表 连接
select * from cdb.dbo.ERPContract E
where not exists
(
select 1 from
(
select R.id from
(
SELECT C.id FROM cdb.dbo.ERPContract C
where CRMODE = 'DEL01'
) T
left join cdb.dbo.ERPContract R
on T.CUSTOMER=R.CUSTOMER AND
T.STYLE=R.STYLE AND
T.KIND=R.KIND AND
T.COLOR=R.COLOR AND
R.CRMODE = 'INS01' AND R.ID<T.ID
)A where E.Id = A.id
) declare ids table
(
id varchar(50)
)
insert into ids
SELECT C.id FROM cdb.dbo.ERPContract C
where CRMODE = 'DEL01'select * from cdb.dbo.ERPContract E
where not exists
(
select 1 from
(
select R.id from
ids T
left join cdb.dbo.ERPContract R
on T.CUSTOMER=R.CUSTOMER AND
T.STYLE=R.STYLE AND
T.KIND=R.KIND AND
T.COLOR=R.COLOR AND
R.CRMODE = 'INS01' AND R.ID<T.ID
)A where E.Id = A.id
) 试试上面两个