--情景：一张表中有ID(主键自增列) GID(部门编号) Name(姓名) GPID()
--现在需要按照部门编号分组，然后对同一部门的人员的GPID字段变为 GPID=GID+'一个四个长度的字符串'
--该字符串要求为 根据每个部门的人数依次为0001，0002，0003.....到该部门人数的最大值；
--假设任何一人只属于一个部门
代码如下所示
create table t
(
id int,
GID varchar(10),
name varchar(200),
GPID varchar(100)
)
insert into t values(1,'G001','name1',null)
insert into t values(2,'G002','name2',null)
insert into t values(3,'G001','name3',null)
insert into t values(4,'G002','name4',null)
insert into t values(5,'G003','name5',null)
insert into t values(6,'G003','name6',null)
--测试数据如下：
--ID GID Name GPID
--1 G001 张三
--2 G002 张2
--3 G001 李四
--4 G0002 王武
--5 G0002 历经
--6 G002 谢的
--7 G002 开心
--8 G003 你好
--9 G00112 不好
--10 G00113 测试
--11 G0002 王武1
--12 G0002 王武2
--13 G0002 王武3
--14 G0002 王武4
--15 G0002 王武5
--16 G0002 王武6
--17 G0002 王武7
--18 G0002 王武8
--19 G0002 王武9
--20 G0002 王武0
--最终希望生成的结果如下
-- ID GID GPID
-- 4 G0002 G00020001
-- 5 G0002 G00020002
-- 11 G0002 G00020003
-- 12 G0002 G00020004
-- 13 G0002 G00020005
-- 14 G0002 G00020006
-- 15 G0002 G00020007
-- 16 G0002 G00020008
-- 17 G0002 G00020009
-- 18 G0002 G00020010
-- 19 G0002 G00020011
-- 20 G0002 G00020012
-- 1 G001 G0010001
-- 3 G001 G0010002
-- 9 G00112 G001120001
-- 10 G00113 G001130001
-- 2 G 002 G0020001
-- 6 G002 G0020002
-- 7 G002 G0020003
-- 8 G003 G0030001
--sql语句：
SELECT ID,GID, GID+right(replicate('0',3)+cast(convert(nvarchar(100),DENSE_RANK() over(partition by GID order by ID)) as varchar(4)),4) as GPID
FROM Dep
关键点：
REPLICATE()
REPLICATE
以指定的次数重复字符表达式。
语法
REPLICATE ( character_expression , integer_expression )
参数
character_expression
由字符数据组成的字母数字表达式。character_expression 可以是常量或变量，也可以是字符列或二进制数据列。
integer_expression
是正整数。如果 integer_expression 为负，则返回空字符串。
返回类型
varchar
character_expression 必须为可隐性转换为 varchar 的数据类型。否则，使用 CAST 函数显式转换 character_expression。
注释
兼容级别可能影响返回值。有关更多信息，请参见 sp_dbcmptlevel。
示例
A. 使用 REPLICATE
下例重复两遍每个作者的名字。
USE pubs
SELECT REPLICATE(au_fname, 2)
FROM authors
ORDER BY au_fname
下面是结果集：
----------------------
AbrahamAbraham
AkikoAkiko
AlbertAlbert
AnnAnn
AnneAnne
BurtBurt
CharleneCharlene
CherylCheryl
DeanDean
DirkDirk
HeatherHeather
InnesInnes
JohnsonJohnson
LiviaLivia
MarjorieMarjorie
MeanderMeander
MichaelMichael
MichelMichel
MorningstarMorningstar
ReginaldReginald
SherylSheryl
StearnsStearns
SylviaSylvia
(23 row(s) affected)
DENSE_RANK():
语法
DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注
如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。
整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的第一行。
参数
< partition_by_clause >
将 FROM 子句所生成的结果集划分为数个将应用 DENSE_RANK 函数的分区。
< order_by_clause >
确定将 DENSE_RANK 值应用于分区中各行的顺序。
返回类型
bigint
示例
以下示例返回各位置上产品数量的 DENSE_RANK。
复制代码
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as DENSE_RANK
FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY Name;
GO
