每个分类取最新的几条的SQL实现代码

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/–>if object_id(N’company’) is not null
drop table company
go
create table company
(
companyname varchar(2),
product varchar(60)
)
–公司1
insert into company
select ‘A’,’A1′ union
select ‘A’,’A2′ union
select ‘A’,’A3′ union
select ‘A’,’A4′ union
select ‘A’,’A5′ union
select ‘A’,’A6′ union
select ‘A’,’A7′ union
select ‘A’,’A8′ union
select ‘A’,’A9′ union
select ‘A’,’A10′
–公司2
insert into company
select ‘B’,’B1′ union
select ‘B’,’B2′ union
select ‘B’,’B3′ union
select ‘B’,’B4′ union
select ‘B’,’B5′ union
select ‘B’,’B6′ union
select ‘B’,’B7′ union
select ‘B’,’B8′ union
select ‘B’,’B9′ union
select ‘B’,’B10′
–公司3
insert into company
select ‘C’,’C1′ union
select ‘C’,’C2′ union
select ‘C’,’C3′ union
select ‘C’,’C4′ union
select ‘C’,’C5′ union
select ‘C’,’C6′ union
select ‘C’,’C7′ union
select ‘C’,’C8′ union
select ‘C’,’C9′ union
select ‘C’,’C10′
–公司4
insert into company
select ‘D’,’D1′ union
select ‘D’,’D2′ union
select ‘D’,’D3′ union
select ‘D’,’D4′ union
select ‘D’,’D5′ union
select ‘D’,’D6′ union
select ‘D’,’D7′ union
select ‘D’,’D8′ union
select ‘D’,’D9′ union
select ‘D’,’D10′
–公司5
insert into company
select ‘E’,’E1′ union
select ‘E’,’E2′ union
select ‘E’,’E3′ union
select ‘E’,’E4′ union
select ‘E’,’E5′ union
select ‘E’,’E6′ union
select ‘E’,’E7′ union
select ‘E’,’E8′ union
select ‘E’,’E9′ union
select ‘E’,’E10′
–公司6
insert into company
select ‘F’,’F1′ union
select ‘F’,’F2′ union
select ‘F’,’F3′ union
select ‘F’,’F4′ union
select ‘F’,’F5′ union
select ‘F’,’F6′ union
select ‘F’,’F7′ union
select ‘F’,’F8′ union
select ‘F’,’F9′ union
select ‘F’,’F10′
–公司7
insert into company
select ‘G’,’G1′ union
select ‘G’,’G2′ union
select ‘G’,’G3′ union
select ‘G’,’G4′ union
select ‘G’,’G5′ union
select ‘G’,’G6′ union
select ‘G’,’G7′ union
select ‘G’,’G8′ union
select ‘G’,’G9′ union
select ‘G’,’G10′
–公司8
insert into company
select ‘H’,’H1′ union
select ‘H’,’H2′ union
select ‘H’,’H3′ union
select ‘H’,’H4′ union
select ‘H’,’H5′ union
select ‘H’,’H6′ union
select ‘H’,’H7′ union
select ‘H’,’H8′ union
select ‘H’,’H9′ union
select ‘H’,’H10′
–公司9
insert into company
select ‘I’,’I1′ union
select ‘I’,’I2′ union
select ‘I’,’I3′ union
select ‘I’,’I4′ union
select ‘I’,’I5′ union
select ‘I’,’I6′ union
select ‘I’,’I7′ union
select ‘I’,’I8′ union
select ‘I’,’I9′ union
select ‘I’,’I10′
–公司10
insert into company
select ‘J’,’J1′ union
select ‘J’,’J2′ union
select ‘J’,’J3′ union
select ‘J’,’J4′ union
select ‘J’,’J5′ union
select ‘J’,’J6′ union
select ‘J’,’J7′ union
select ‘J’,’J8′ union
select ‘J’,’J9′ union
select ‘J’,’J10′
IF (select Object_id(‘Tempdb..#t’)) IS NULL
select identity(int,1,1) as id,* into #t from company
order by left(product,1),cast(substring(product,2,2) as int)
if object_id(N’getdata’,’P’) is not null
drop table getdata
go
create proc getdata
@num1 int –第几页
as
begin
select companyname,product from
(
select row_number() over (partition by companyname order by id) as 序号,*
from #t
) a
where 序号=@num1
order by companyname
end
go
getdata 4
go
DROP procedure getdata