如何查询出每个人的倒数第二条记录费用明细表：tbl_person_listLidPidLmoneyPmoneyLDate------------------
如何查询出每个人的倒数第二条记录
费用明细表：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 行）*/
[解决办法]
- SQL code
SELECT * FROM Ta a WHERE (SELECT count(1) FROM Ta 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 awhere Lid = (select min(Lid) from (select top 2 Lid from @a where Pid=a.Pid order by Lid desc )T)（所影响的行数为 8 行）Lid Pid Lmoney Pmoney LDate ----------- ----------- ----------- ----------- ------------------------------------------------------ 2 2 50 50 2008-02-11 00:00:004 1 50 100 2008-02-13 00:00:006 3 100 150 2008-02-13 00:00:00（所影响的行数为 3 行）
[解决办法]
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,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
order by pid
drop table tb
[解决办法]
select * from tbl_person_list
where lid in
(select max(lid)-1 from tbl_person_list b group by pid)
order by pid,lid