费用明细表：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) mwhere 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 awhere (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') goselect 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) mwhere px = 2order by piddrop table tb/*Lid Pid Lmoney Pmoney LDate ----------- ----------- ----------- ----------- ------------------------------------------------------ 4 1 50 100 2008-02-13 00:00:00.0002 2 50 50 2008-02-11 00:00:00.0006 3 100 150 2008-02-13 00:00:00.000（所影响的行数为 3 行）*/