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

2022-11-12 09:48:37
内容摘要
这篇文章主要为大家详细介绍了SQL Server每个分类取最新的几条的SQL实现代码，具有一定的参考价值，可以用来参考一下。 对此感兴趣的朋友，看看idc笔记做的技术笔记！CREATE TABLE
文章正文

这篇文章主要为大家详细介绍了SQL Server每个分类取最新的几条的SQL实现代码，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

CREATE TABLE table1( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [class] int not null, [date] datetime not null)class 表示分类编号。 分类数不固定， 至少有上千种分类date 表示该条记录被更新的时间我们现在想获得每个分类最新被更新的5条记录。解决方案select id,name,class,date from(select id,name,class,date ,row_number() over(partition by class order by date desc)as rowindex from table1) awhere rowindex <= 5create table #temp(company varchar(50),product varchar(50),inputDate datetime)insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车1','2010-8-1')insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车2','2010-8-1')insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车3','2010-8-1')insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车4','2010-8-1')insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车5','2010-7-1')insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车1','2010-8-1')insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车2','2010-8-1')insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车3','2010-8-1')insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车4','2010-8-1')insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车1','2010-8-1')insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车2','2010-8-1')insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车3','2010-8-1')insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车4','2010-8-1')insert into #temp(company,product,inputDate) values('天津旺旺有限公司','汽车4','2010-8-1')insert into #temp(company,product,inputDate) values('天津旺旺有限公司','汽车5','2010-8-1')select * from #tempcreate proc getdata@num intasbeginselect top 4 * from(select ( select count(*) from #temp where company=a.company and product<=a.product) as 序号,a.company,a.product,a.inputDatefrom #temp a) bwhere 序号>=@numorder by 序号,inputDate descendgogetdata 2/*结果1 杭州大明有限公司 汽车1 2010-08-01 00:00:00.0001 北京小科有限公司 汽车1 2010-08-01 00:00:00.0001 上海有得有限公司 汽车1 2010-08-01 00:00:00.0001 天津旺旺有限公司 汽车4 2010-08-01 00:00:00.0002 天津旺旺有限公司 汽车5 2010-08-01 00:00:00.0002 上海有得有限公司 汽车2 2010-08-01 00:00:00.0002 北京小科有限公司 汽车2 2010-08-01 00:00:00.0002 杭州大明有限公司 汽车2 2010-08-01 00:00:00.0003 杭州大明有限公司 汽车3 2010-08-01 00:00:00.0003 北京小科有限公司 汽车3 2010-08-01 00:00:00.0003 上海有得有限公司 汽车3 2010-08-01 00:00:00.0004 北京小科有限公司 汽车4 2010-08-01 00:00:00.0004 北京小科有限公司 汽车4 2010-08-01 00:00:00.0004 上海有得有限公司 汽车4 2010-08-01 00:00:00.0004 杭州大明有限公司 汽车4 2010-08-01 00:00:00.0005 杭州大明有限公司 汽车5 2010-07-01 00:00:00.000*/--sql2005create proc getdata2005@num intasbeginselect top 4 * from(select row_number() over (partition by company order by product ) as 序号,a.company,a.product,a.inputDatefrom #temp a) bwhere 序号>=@numorder by 序号,inputDate descendgetdata2005 4select * from #tempselect ( select count(*) from #temp where company+ product<=a.company+a.product) as 序号,a.company,a.product,a.inputDate,a.company+a.product as 唯一标志一行from #temp aorder by company,product

代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
<code>
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
</code>

注：关于SQL Server每个分类取最新的几条的SQL实现代码的内容就先介绍到这里，更多相关文章的可以留意

代码注释
上一篇SQL Server SQL 中having 和where的区别分析下一篇SQL Server中通过reverse取某个最后一次出现的符号后面的内容(字符串反转)

作者：喵哥笔记

IDC笔记

学的不仅是技术，更是梦想！