日期:2014-05-18  浏览次数:20434 次

数据查询问题
有个表:
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)