日期:2014-05-16 浏览次数:20573 次
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-20 11:34:20
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([姓名] nvarchar(4),[课程] nvarchar(4),[分数] int)
insert [huang]
select N'张三',N'语文',90 union all
select N'李四',N'数学',100 union all
select N'张三',N'数学',95 union all
select N'李四',N'语文',85
--------------生成数据--------------------------
select * ,ROW_NUMBER()OVER(PARTITION BY [课程] ORDER BY [课程] )id
from [huang]
ORDER BY [课程]
----------------结果----------------------------
/*
姓名 课程 分数 id
---- ---- ----------- --------------------
李四 数学 100 1
张三 数学 95 2
李四 语文 85 1
张三 语文 90 2
*/
select row_number()over(partition by 课程 order by 分数) as 序号 ,* from tb
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-20 11:37:17
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(4),[课程] varchar(4),[分数] int)
insert [tb]
select '张三','语文',90 union all
select