时间:2021-07-01 10:21:17 帮助过:31人阅读
每个分类取最新的几条的SQL实现代码,需要的朋友可以参考下
CREATE TABLE table1( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [class] int not null, [date] datetime not null)class 表示分类编号。 分类数不固定, 至少有上千种分类
- <br>Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->if object_id(N'company') is not null <br>drop table company <br>go <br>create table company <br>( <br>companyname varchar(2), <br>product varchar(60) <br>) <br>--公司1 <br>insert into company <br>select 'A','A1' union <br>select 'A','A2' union <br>select 'A','A3' union <br>select 'A','A4' union <br>select 'A','A5' union <br>select 'A','A6' union <br>select 'A','A7' union <br>select 'A','A8' union <br>select 'A','A9' union <br>select 'A','A10' <br>--公司2 <br>insert into company <br>select 'B','B1' union <br>select 'B','B2' union <br>select 'B','B3' union <br>select 'B','B4' union <br>select 'B','B5' union <br>select 'B','B6' union <br>select 'B','B7' union <br>select 'B','B8' union <br>select 'B','B9' union <br>select 'B','B10' <br>--公司3 <br>insert into company <br>select 'C','C1' union <br>select 'C','C2' union <br>select 'C','C3' union <br>select 'C','C4' union <br>select 'C','C5' union <br>select 'C','C6' union <br>select 'C','C7' union <br>select 'C','C8' union <br>select 'C','C9' union <br>select 'C','C10' <br>--公司4 <br>insert into company <br>select 'D','D1' union <br>select 'D','D2' union <br>select 'D','D3' union <br>select 'D','D4' union <br>select 'D','D5' union <br>select 'D','D6' union <br>select 'D','D7' union <br>select 'D','D8' union <br>select 'D','D9' union <br>select 'D','D10' <br>--公司5 <br>insert into company <br>select 'E','E1' union <br>select 'E','E2' union <br>select 'E','E3' union <br>select 'E','E4' union <br>select 'E','E5' union <br>select 'E','E6' union <br>select 'E','E7' union <br>select 'E','E8' union <br>select 'E','E9' union <br>select 'E','E10' <br>--公司6 <br>insert into company <br>select 'F','F1' union <br>select 'F','F2' union <br>select 'F','F3' union <br>select 'F','F4' union <br>select 'F','F5' union <br>select 'F','F6' union <br>select 'F','F7' union <br>select 'F','F8' union <br>select 'F','F9' union <br>select 'F','F10' <br>--公司7 <br>insert into company <br>select 'G','G1' union <br>select 'G','G2' union <br>select 'G','G3' union <br>select 'G','G4' union <br>select 'G','G5' union <br>select 'G','G6' union <br>select 'G','G7' union <br>select 'G','G8' union <br>select 'G','G9' union <br>select 'G','G10' <br>--公司8 <br>insert into company <br>select 'H','H1' union <br>select 'H','H2' union <br>select 'H','H3' union <br>select 'H','H4' union <br>select 'H','H5' union <br>select 'H','H6' union <br>select 'H','H7' union <br>select 'H','H8' union <br>select 'H','H9' union <br>select 'H','H10' <br>--公司9 <br>insert into company <br>select 'I','I1' union <br>select 'I','I2' union <br>select 'I','I3' union <br>select 'I','I4' union <br>select 'I','I5' union <br>select 'I','I6' union <br>select 'I','I7' union <br>select 'I','I8' union <br>select 'I','I9' union <br>select 'I','I10' <br>--公司10 <br>insert into company <br>select 'J','J1' union <br>select 'J','J2' union <br>select 'J','J3' union <br>select 'J','J4' union <br>select 'J','J5' union <br>select 'J','J6' union <br>select 'J','J7' union <br>select 'J','J8' union <br>select 'J','J9' union <br>select 'J','J10' <br>IF (select Object_id('Tempdb..#t')) IS NULL <br>select identity(int,1,1) as id,* into #t from company <br>order by left(product,1),cast(substring(product,2,2) as int) <br>if object_id(N'getdata','P') is not null <br>drop table getdata <br>go <br>create proc getdata <br>@num1 int --第几页 <br>as <br>begin <br>select companyname,product from <br>( <br>select row_number() over (partition by companyname order by id) as 序号,* <br>from #t <br>) a <br>where 序号=@num1 <br>order by companyname <br>end <br>go <br>getdata 4 <br>go <br>DROP procedure getdata