27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-08-18 11:26:37
-- 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 Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(6),[班级] varchar(20))
insert [tb]
select '刘曦羲','20110101' union all
select '陈程吉','20110101' union all
select '单杰妮','20110101' union all
select '陈俏颖','20110102' union all
select '郑晋斌','20110102' union all
select '陶咪箫','20110102' union all
select '陈彦名','20110102' union all
select '陈洪璐','20110103' union all
select '陈笑影','20110103' union all
select '周宇豪','20110103' union all
select '陈俞妃','20110103' union all
select '刘丹丹','20110103' union all
select '黄强','20110103' union all
select '叶力鸣','20110103'
--------------开始查询--------------------------
select ID=identity(int,1,1),* into #t from tb
select
姓名,班级,
学号=ltrim(班级+right('100'+(select COUNT(distinct id)+1 from #t where 班级=t.班级 and ID<t.id),2))
from
#t t
drop table #t
----------------结果----------------------------
/* 姓名 班级 学号
------ -------------------- ------------------------
刘曦羲 20110101 2011010101
陈程吉 20110101 2011010102
单杰妮 20110101 2011010103
陈俏颖 20110102 2011010201
郑晋斌 20110102 2011010202
陶咪箫 20110102 2011010203
陈彦名 20110102 2011010204
陈洪璐 20110103 2011010301
陈笑影 20110103 2011010302
周宇豪 20110103 2011010303
陈俞妃 20110103 2011010304
刘丹丹 20110103 2011010305
黄强 20110103 2011010306
叶力鸣 20110103 2011010307
(14 行受影响)
*/
select 姓名,班级,
rtrim(班级)+right('00'+rtrim(row_number() over (partition by 班级 order by getdate())),2) as 学号
from tb
select 姓名,班级,
right(100+row_number() over (partition by 班级 order by getdate()),2) as 学号
from tb