row_number()over()分组排序

row_number()over()

表数据

rowID	order_id	dish_id	quantity	price
1	O001	D001	1	20.00
2	O001	D002	1	5.00
3	O002	D001	1	20.00
4	O002	D003	3	10.00
5	O003	D010	2	5.00
6	O002	D100	1	3.00
7	O003	D200	1	30.00

建表语句

CREATE TABLE [order_detail](
	[rowID] [bigint] IDENTITY(1,1) NOT NULL,
	[order_id] [nvarchar](50) NULL,
	[dish_id] [nvarchar](50) NULL,
	[quantity] [int] NULL,
	[price] [decimal](18, 2) NULL
) 


INSERT [order_detail] ([rowID], [order_id], [dish_id], [quantity], [price]) VALUES (1, N'O001', N'D001', 1, CAST(20.00 AS Decimal(18, 2)))
INSERT [order_detail] ([rowID], [order_id], [dish_id], [quantity], [price]) VALUES (2, N'O001', N'D002', 1, CAST(5.00 AS Decimal(18, 2)))
INSERT [order_detail] ([rowID], [order_id], [dish_id], [quantity], [price]) VALUES (3, N'O002', N'D001', 1, CAST(20.00 AS Decimal(18, 2)))
INSERT [order_detail] ([rowID], [order_id], [dish_id], [quantity], [price]) VALUES (4, N'O002', N'D003', 3, CAST(10.00 AS Decimal(18, 2)))
INSERT [order_detail] ([rowID], [order_id], [dish_id], [quantity], [price]) VALUES (5, N'O003', N'D010', 2, CAST(5.00 AS Decimal(18, 2)))
INSERT [order_detail] ([rowID], [order_id], [dish_id], [quantity], [price]) VALUES (6, N'O002', N'D100', 1, CAST(3.00 AS Decimal(18, 2)))
INSERT [order_detail] ([rowID], [order_id], [dish_id], [quantity], [price]) VALUES (7, N'O003', N'D200', 1, CAST(30.00 AS Decimal(18, 2)))

生成行号排序

/*
筛选后生成行号排序
*/
select *,row_number()over(order by dish_id desc) row_num
from order_detail where price between 5 and 20

rowID	order_id	dish_id	quantity	price	row_num
5	O003	D010	2	5.00	1
4	O002	D003	3	10.00	2
2	O001	D002	1	5.00	3
3	O002	D001	1	20.00	4
1	O001	D001	1	20.00	5

此种方式可以实现分页，但性能不高，SQL server2012及以上版本推荐使用OFFSET/FETCH NEXT;

/*
生成行号排序进行分页，取11-20数据
*/
select *,row_number()over(order by dish_id desc) row_num
from order_detail where row_num between 11 and 20

生成组内行号排序

/*
筛选后根据订单号生成组内行号排序
*/
select *,row_number()over(partition by order_id order by dish_id desc) part_num
from order_detail where price between 5 and 20

rowID	order_id	dish_id	quantity	price	part_num
2	O001	D002	1	5.00	1
1	O001	D001	1	20.00	2
4	O002	D003	3	10.00	1
3	O002	D001	1	20.00	2
5	O003	D010	2	5.00	1