有个表:
id time balance
1 2007-9-1 100
2 2007-9-2 90
3 2007-9-3 80
4 2007-9-4 150
5 2007-9-5 140数据如上,balance是根据日期越大而越小的,只有id为4这个记录不是,本来记录为4的记录的balance应该小于80的,而它却比80了,请问怎么才能找出这个id为4的记录?注:表的数据量很大,需要考虑性能问题。 id不是连续的,也不一定是从小到大排序的
id time balance
1 2007-9-1 100
2 2007-9-2 90
3 2007-9-3 80
4 2007-9-4 150
5 2007-9-5 140数据如上,balance是根据日期越大而越小的,只有id为4这个记录不是,本来记录为4的记录的balance应该小于80的,而它却比80了,请问怎么才能找出这个id为4的记录?注:表的数据量很大,需要考虑性能问题。 id不是连续的,也不一定是从小到大排序的
(
[id] int,
[time] datetime,
balance int
)insert A select 1,'2007-9-1',100
insert A select 2,'2007-9-2',90
insert A select 3,'2007-9-3',80
insert A select 4,'2007-9-4',150
insert A select 5,'2007-9-5',140
select *
from A T
where exists(select 1 from (select top 1 * from A where [time]<T.[time] order by [time] DESC) W where T.balance>W.balance)
insert into tb values(1 , '2007-9-1', 100)
insert into tb values(2 , '2007-9-2', 90)
insert into tb values(3 , '2007-9-3', 80)
insert into tb values(4 , '2007-9-4', 150)
insert into tb values(5 , '2007-9-5', 140)select * , px = identity(int,1,1) into tmp from tb order by time
select b.id,convert(varchar(10),b.time,120) time , b.balance from tmp a,tmp b where a.id = b.id - 1 and a.balance < b.balancedrop table tb,tmp/*
id time balance
----------- ---------- -----------
4 2007-09-04 150(所影响的行数为 1 行)
*/
declare @T table(id int,time datetime,balance int)
insert @T
select 1,'2007-9-1',100 union all
select 2,'2007-9-2',90 union all
select 3,'2007-9-3',80 union all
select 4,'2007-9-4',150 union all
select 5,'2007-9-5',140select * from @T a where balance > (select max(balance) from @T where time<a.time)
select b.id,convert(varchar(10),b.time,120) time , b.balance from tmp a,tmp b where a.id = b.id - 1 and a.balance < b.balance
-------------------------
因数据量真的很大,如果再产生个临时表,把数据复制到tmp不太现实