create table tb(帐表No int, 修改No int, 内容 varchar(10))
insert into tb values(1 , 0 , 'aa')
insert into tb values(1 , 1 , 'bb')
insert into tb values(1 , 2 , 'cc')
insert into tb values(1 , 3 , 'dd')
insert into tb values(2 , 0 , 'xx')
insert into tb values(2 , 1 , 'yy')
insert into tb values(3 , 0 , 'ee')
insert into tb values(3 , 1 , 'ff')
insert into tb values(3 , 2 , 'gg')
insert into tb values(4 , 0 , 'mm')
goselect m.帐表No , m.内容 , n.内容 from
(select * , px = (select count(1) from tb where 帐表No = t.帐表No and 修改No > t.修改No ) + 1 from tb t where 帐表No in (select 帐表No from tb group by 帐表No having count(*) > 2)) m ,
(select * , px = (select count(1) from tb where 帐表No = t.帐表No and 修改No > t.修改No ) + 1 from tb t where 帐表No in (select 帐表No from tb group by 帐表No having count(*) > 2)) n
where m.帐表No = n.帐表No and m.px = 2 and n.px = 1drop table tb/*
帐表No 内容 内容
----------- ---------- ----------
1 cc dd
3 ff gg(所影响的行数为 2 行)
*/
insert into tb values(1 , 0 , 'aa')
insert into tb values(1 , 1 , 'bb')
insert into tb values(1 , 2 , 'cc')
insert into tb values(1 , 3 , 'dd')
insert into tb values(2 , 0 , 'xx')
insert into tb values(2 , 1 , 'yy')
insert into tb values(3 , 0 , 'ee')
insert into tb values(3 , 1 , 'ff')
insert into tb values(3 , 2 , 'gg')
insert into tb values(4 , 0 , 'mm')
goselect m.帐表No , m.内容 , n.内容 from
(select * , px = (select count(1) from tb where 帐表No = t.帐表No and 修改No > t.修改No ) + 1 from tb t where 帐表No in (select 帐表No from tb group by 帐表No having count(*) > 2)) m ,
(select * , px = (select count(1) from tb where 帐表No = t.帐表No and 修改No > t.修改No ) + 1 from tb t where 帐表No in (select 帐表No from tb group by 帐表No having count(*) > 2)) n
where m.帐表No = n.帐表No and m.px = 2 and n.px = 1drop table tb/*
帐表No 内容 内容
----------- ---------- ----------
1 cc dd
3 ff gg(所影响的行数为 2 行)
*/
create Table T
(
[帐表No] int,
[修改NO] int,
内容 varchar(10)
)
Goinsert T select 1,0,'aa'
insert T select 1,1,'bb'
insert T select 1,2,'cc'
insert T select 1,3,'dd'
insert T select 2,0,'xx'
insert T select 2,1,'yy'
insert T select 3,0,'ee'
insert T select 3,1,'ff'
insert T select 3,2,'gg'
insert T select 4,0,'mm'
GOcreate Function T_Str(@NO int)
returns varchar(1000)
as
begin
declare @T_Str varchar(1000)
Set @T_Str=''
select top 2 @T_Str= @T_Str + 内容 + ' ' from T where [帐表No]= @NO order by [修改NO] DESC
return @T_Str
end
Goselect [帐表No],dbo.T_Str([帐表No])
from T
group by [帐表No]
having count(1)>2