数据查询问题
有个表:
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不是连续的,也不一定是从小到大排序的
------解决方案--------------------select * from tb1 b where b.balance > (select top a.1 balance from tb1 a where a.id <b.id order by a.id desc )
------解决方案--------------------create table A
(
[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)
------解决方案--------------------create table tb(id int,time datetime,balance int)
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.balance
drop table tb,tmp
/*
id time balance
----------- ---------- -----------
4 2007-09-04 150
(所影响的行数为 1 行)
*/
------解决方案----------------------原始数据:@T
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 ',140
select * from @T a where balance > (select max(balance) from @T where time <a.time)