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

如何查询出每个人的倒数第二条记录
费用明细表:tbl_person_list  

Lid Pid Lmoney Pmoney LDate  
---------------------------------------  
1   1   50     50  2008-2-11  
2   2   50     50  2008-2-11  
3   3   50     50  2008-2-11  
4   1   50     100 2008-2-13  
5   2   -56     -6  2008-2-13  
6   3   100     150  2008-2-13  
7   1   100     200  2008-2-14  
8   3   150     300  2008-2-14  

现要查询出每个人的倒数第二条记录? 


------解决方案--------------------
SQL code
select Lid,Pid,Lmoney,Pmoney,LDate from
(  
  select * , px = (select count(1) from tb where pid = t.pid and lid > t.lid) + 1 from tb t
) m
where px = 2

------解决方案--------------------
SQL code

create table tbl_person_list(lid int,pid int,lmoney int,pmoney int,ldate datetime)
insert into tbl_person_list select 1,1,50,50,'2008-2-11'
insert into tbl_person_list select 2,2,50,50,'2008-2-11'
insert into tbl_person_list select 3,3,50,50,'2008-2-11'
insert into tbl_person_list select 4,1,50,100,'2008-2-13'
insert into tbl_person_list select 5,2,-56,-6,'2008-2-13'
insert into tbl_person_list select 6,3,100,150,'2008-2-13'
insert into tbl_person_list select 7,1,100,200,'2008-2-14'
insert into tbl_person_list select 8,3,150,300,'2008-2-14'


select * from tbl_person_list a
where (select count(distinct ldate) from tbl_person_list where pid=a.pid and ldate>=a.ldate)=2

------解决方案--------------------
SQL code
DECLARE @a TABLE(Lid INT,Pid INT,Lmoney INT,Pmoney INT,LDate SMALLDATETIME)      
INSERT @a SELECT  1,1,50,50,'2008-2-11'   
UNION ALL SELECT  2,2,50,50,'2008-2-11'   
UNION ALL SELECT  3,3,50,50,'2008-2-11'   
UNION ALL SELECT  4,1,50,100,'2008-2-13'   
UNION ALL SELECT  5,2,-56,-6,'2008-2-13'   
UNION ALL SELECT  6,3,100,150,'2008-2-13'   
UNION ALL SELECT  7,1,100,200,'2008-2-14'   
UNION ALL SELECT  8,3,150,300,'2008-2-14'   


SELECT * FROM @a a
WHERE (SELECT count(1) FROM @a WHERE Pid=a.Pid AND LDate>=a.Ldate)=2

--result
/*Lid    Pid    Lmoney    Pmoney    LDate                         
----------- ----------- ----------- ----------- ------------------------------------------------------
2      2      50      50      2008-02-11 00:00:00
4      1      50      100    2008-02-13 00:00:00
6      3      100    150    2008-02-13 00:00:00

(所影响的行数为 3 行)

*/

------解决方案--------------------
SQL code
create table tb(Lid int,Pid int,Lmoney int,Pmoney int,LDate datetime)
insert into tb values(1, 1, 50 , 50 , '2008-2-11') 
insert into tb values(2, 2, 50 , 50 , '2008-2-11') 
insert into tb values(3, 3, 50 , 50 , '2008-2-11') 
insert into tb values(4, 1, 50 , 100, '2008-2-13') 
insert into tb values(5, 2, -56, -6 , '2008-2-13') 
insert into tb values(6, 3, 100, 150, '2008-2-13') 
insert into tb values(7, 1, 100, 200, '2008-2-14') 
insert into tb values(8, 3, 150, 300, '2008-2-14') 
go

select Lid,Pid,Lmone