if OBJECT_ID('Tempdb..#T') is not null
drop table #T
create table #T
(
ItemNo nvarchar(10),
ItemName varchar(100),
GroupNo nvarchar(10),
GroupName nvarchar(100)
)
insert into #T(ItemNo, ItemName, GroupNo, GroupName)
select 'A01', '电脑', 'A', '设备' union all
select 'A02', '桌子', 'A', '设备' union all
select 'A03', '电话', 'A', '设备' union all
select 'B01', '尺子', 'B', '文具' union all
select 'B02', '铅笔', 'B', '文具' union all
select 'B03', '介刀', 'B', '文具'
-- 简单排序
select Row_Number() over(Order by ItemNo) as RowNo, * from #T order by RowNo
-- 组内排序
select Row_Number() over(Partition by GroupNo Order by ItemNo) as RowNo, * from #T order by GroupNo, RowNo
-- 取第2到4条记录(稍作修改可以实现分页显示记录)
select * from (select Row_Number() over(Order by ItemNo) as RowNo, * from #T) twhere RowNo>=2 and RowNo<=4 order by RowNo
