如下表数据
COL1 COL2
A 12
B 12
A 1
C 4
B 5
B 7
..............
查询时在前面添加分组序号;结果如下:
ID COL1 COL2
1 A 12
1 A 1
2 B 12
2 B 5
2 B 7
3 C 4
..............有多少种方法可以实现这种结果,那种是最高效、最简洁的方法
COL1 COL2
A 12
B 12
A 1
C 4
B 5
B 7
..............
查询时在前面添加分组序号;结果如下:
ID COL1 COL2
1 A 12
1 A 1
2 B 12
2 B 5
2 B 7
3 C 4
..............有多少种方法可以实现这种结果,那种是最高效、最简洁的方法
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([COL1] varchar(1),[COL2] int)
insert [TB]
select 'A',12 union all
select 'B',12 union all
select 'A',1 union all
select 'C',4 union all
select 'B',5 union all
select 'B',7
GO--> 查询结果
SELECT ID=ROW_NUMBER() OVER (partition by COL1 ORDER BY COL2),*
FROM [TB]
SELECT ID=RANK() OVER ( ORDER BY COL1),*
FROM [TB]
SELECT ID=Dense_Rank () OVER ( ORDER BY COL1),*
FROM [TB]
SELECT ID=NTILE(2) OVER ( ORDER BY COL1),*
FROM [TB]
--> 删除表格
--DROP TABLE [TB]