用一条sql语句进行排序好的每十条记录分组
现在有一个学生分数表,要进行分组.
表是
成绩(学号,成绩)
学号 成绩
001 20
002 40
003 30
004 50
005 55
006 80
007 65
008 75
009 48
010 65
011 78
要进行成绩,学号排序后的每十条分组
学号 成绩 分组
001 20 1
003 30 1
002 40 1
009 48 1
004 50 1
005 55 1
007 65 1
010 65 1
008 75 1
011 78 1
006 80 2
[解决办法]
--> 测试数据：[成绩]
if object_id('[成绩]') is not null drop table [成绩]
GO
create table [成绩]([学号] varchar(3),[成绩] int)
insert [成绩]
select '001',20 union all
select '002',40 union all
select '003',30 union all
select '004',50 union all
select '005',55 union all
select '006',80 union all
select '007',65 union all
select '008',75 union all
select '009',48 union all
select '010',65 union all
select '011',78
select *,分组=(ROW_NUMBER() OVER(ORDER BY [学号])-1)/10+1 from [成绩]
/*
学号 成绩 分组
---- ----------- --------------------
001 20 1
002 40 1
003 30 1
004 50 1
005 55 1
006 80 1
007 65 1
008 75 1
009 48 1
010 65 1
011 78 2
(11 行受影响)
*/
drop table [成绩]
[解决办法]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-11 09:08:26
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([学号] varchar(3),[成绩] int)
insert [test]
select '001',20 union all
select '002',40 union all
select '003',30 union all
select '004',50 union all
select '005',55 union all
select '006',80 union all
select '007',65 union all
select '008',75 union all
select '009',48 union all
select '010',65 union all
select '011',78
go
;with t
as(
select
row_number()over(order by [成绩],[学号])%10 as px,
row_number()over(order by [成绩],[学号])/10+1 as NT,
*
from
test
)
select
case when px=0 and NT<>0 then NT-1 else NT end as 分组,
[学号],
[成绩]
from
t
/*
分组学号成绩
-----------------------------------------
100120
100330
100240
100948
100450
100555
100765
101065
100875
101178
200680
*/
