SQL Server 学习5（随机数，整数，编号，序号）

最新推荐文章于 2023-01-05 18:18:00 发布

huang714

最新推荐文章于 2023-01-05 18:18:00 发布

阅读量1.1k

点赞数

分类专栏： SQL优化总结

本文链接：https://blog.csdn.net/huang714/article/details/108614593

版权

SQL优化总结专栏收录该内容

34 篇文章 3 订阅

订阅专栏

1.1 SET IDENTITY_INSERT 中的几个问题

--1. 会话中某个表已将此属性设置为ON，当为另一个表发出了SET IDENTITY_INSERT ON 句时将出错
--测试的表
CREATE TABLE ta(id int IDENTITY(1,1),col int)
CREATE TABLE tb(id int IDENTITY(1,1),col int)
GO

--设置 IDENTITY_INSERT 属性
SET IDENTITY_INSERT ta ON
SET IDENTITY_INSERT tb ON
GO

/*======================================================*/

--2. 如果插入记录的标识值大于表的当前标识值，则SQL Server自动将新插入值作为当前标识值使用
--测试的表
CREATE TABLE tb(id int IDENTITY(1,1),col int)

--强制在表中插入标识值
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(10,1)
SET IDENTITY_INSERT tb OFF

INSERT tb(col) VALUES(2)
SELECT * FROM tb
/*--结果
id col
----------------- -----------
10 1
11 2
--*/
GO

/*======================================================*/

--3. 如果插入记录的标识值小于表的当前标识值，则表的当前标识值不受新插入值的影响
--测试的表
CREATE TABLE tb(id int IDENTITY(1,1),col int)
INSERT tb VALUES(1)
INSERT tb VALUES(2)

--强制在表中插入标识值
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(1,11)
SET IDENTITY_INSERT tb OFF

INSERT tb(col) VALUES(3)
SELECT * FROM tb
/*--结果
id col
----------------- -----------
1 1
2 2
1 11
3 3
--*/

1.2 标识列与普通列互相转换的示例

--创建测试表
CREATE TABLE t1(ID int IDENTITY,A int)
GO
--插入记录
INSERT t1 VALUES(1)
GO

--1. 将IDENTITY(标识)列变为普通列
ALTER TABLE t1 ADD ID_temp int
GO

UPDATE t1 SET ID_temp=ID
ALTER TABLE t1 DROP COLUMN ID
EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN'
INSERT t1 VALUES(100,9)
GO

--2. 将普通列变为标识列
CREATE TABLE t1_temp(ID int,A int IDENTITY)
SET IDENTITY_INSERT t1_temp ON
INSERT t1_temp(ID,A) SELECT * FROM t1
SET IDENTITY_INSERT t1_temp OFF
DROP TABLE T1
GO

EXEC sp_rename N't1_temp',N't1'
INSERT t1 VALUES(109999)
GO

--显示处理结果
SELECT * FROM t1
/*--结果:
ID A
----------------- -----------
1 1
100 9
109999 10
--*/

1.3 修改标识值的示例

--测试资料
CREATE TABLE t1(ID int IDENTITY,A int)
INSERT t1 VALUES(1)
INSERT t1 VALUES(2)
INSERT t1 VALUES(3)
DELETE FROM t1 WHERE A=2
GO

--将ID=3的记录的ID值改为2
SET IDENTITY_INSERT t1 ON
INSERT t1(ID,A) SELECT 2,A FROM t1 WHERE ID=3
DELETE FROM t1 WHERE ID=3
SET IDENTITY_INSERT t1 OFF
SELECT * FROM t1
/*--结果
ID A
----------------- -----------
1 1
2 3
--*/

2.1 查表法按日期生成流水号的示例

--以下代码生成的编号长度为12，前6位为日期信息，格式为YYMMDD，后6位为流水号。
--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12)
GO

--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
   DECLARE @dt CHAR(6)
   SELECT @dt=dt FROM v_GetDate
   RETURN(
       SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
       FROM tb WITH(XLOCK,PAGLOCK)
       WHERE BH like @dt+'%')
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

--插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)

--显示结果
SELECT * FROM tb
/*--结果
BH col
------------------- -----------
050405000001 1
050405000002 2
050405000003 4
050405000004 14
--*/

2.2 查表法生成流水号的示例

--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

--插入资料
BEGIN TRAN
   INSERT tb(col) VALUES(1)
   INSERT tb(col) VALUES(2)
   INSERT tb(col) VALUES(3)
   DELETE tb WHERE col=3
   INSERT tb(col) VALUES(4)
   INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN

--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/

2.3 使用编号表按日期生成流水号的示例

--编号表
CREATE TABLE tb_NO(
Name char(2) NOT NULL, --编号种类的名称
Days int NOT NULL, --保存的是该种编号那一天的当前编号
Head nvarchar(10) NOT NULL DEFAULT '', --编号的前缀
CurrentNo int NOT NULL DEFAULT 0, --当前编号
BHLen int NOT NULL DEFAULT 6, --编号数字部分长度
YearMoth int NOT NULL --上次生成编号的年月,格式YYYYMM
DEFAULT CONVERT(CHAR(6),GETDATE(),112),
DESCRIPTION NVARCHAR(50), --编号种类说明
TableName sysname NOT NULL, --当前编号对应的原始表名
KeyFieldName sysname NOT NULL, --当前编号对应的原始表编号字段名
PRIMARY KEY(Name,Days))

--这里以一种单据的7天的资料来做测试
INSERT tb_NO SELECT 'CG',1,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',2,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',3,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',4,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',5,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',6,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',7,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
GO

--获取新编号的存储过程
CREATE PROC p_NextBH
@Name char(2), --编号种类
@Date datetime=NULL, --要获取的当前日期,不指定则为系统当前日期
@BH nvarchar(20) OUTPUT --新编号
AS
IF @Date IS NULL SET @Date=GETDATE()
BEGIN TRAN
   --从编号表中获取新编号
   UPDATE tb_NO SET
       @BH=Head
           +CONVERT(CHAR(6),@Date,12)
           +RIGHT(POWER(10,BHLen)
               +CASE
                   WHEN YearMoth=CONVERT(char(6),@Date,112)
                   THEN CurrentNo+1
                   ELSE 1 END
           ,BHLen),
       CurrentNo=CASE
           WHEN YearMoth=CONVERT(char(6),@Date,112)
           THEN CurrentNo+1
           ELSE 1 END,
       YearMoth=CONVERT(char(6),@Date,112)
   WHERE Name=@Name
       AND Days=DAY(@Date)
       AND YearMoth<=CONVERT(char(6),@Date,112)

   --如果要获取的编号在编号表中已经过期,则直接从原始表中取编号
   IF @@ROWCOUNT=0
   BEGIN
       DECLARE @s nvarchar(4000)
       SELECT @s=N'SELECT @BH='
           +QUOTENAME(Head+CONVERT(CHAR(6),@Date,12),N'''')
           +N'+RIGHT('+CAST(POWER(10,BHLen)+1 as varchar)
           +N'+ISNULL(RIGHT(MAX('+QUOTENAME(KeyFieldName)
           +N'),'+CAST(BHLen as varchar)
           +N'),0),'+CAST(BHLen as varchar)
           +N') FROM '+QUOTENAME(TableName)
           +N' WITH(XLOCK,PAGLOCK) WHERE '
           +QUOTENAME(KeyFieldName)
           +N' like '+QUOTENAME(Head+CONVERT(CHAR(6),@Date,12)+N'%',N'''')
       FROM tb_NO
       WHERE Name=@Name
           AND Days=DAY(@Date)
           AND YearMoth>CONVERT(char(6),@Date,112)
       IF @@ROWCOUNT>0
           EXEC sp_executesql @s,N'@BH nvarchar(20) OUTPUT',@BH OUTPUT
   END
COMMIT TRAN
GO

CREATE TABLE tb(BH char(12))
--获取 CG 的新编号
DECLARE @bh char(12)
EXEC p_NextBH 'CG','2005-1-1',@bh OUT
SELECT @bh
--结果: CG0501010001

EXEC p_NextBH 'CG','2005-1-1',@bh OUT
SELECT @bh
--结果: CG0501010002

EXEC p_NextBH 'CG','2005-1-2',@bh OUT
SELECT @bh
--结果: CG0501020001

EXEC p_NextBH 'CG','2005-2-2',@bh OUT
SELECT @bh
--结果: CG0402020001

EXEC p_NextBH 'CG','2004-2-2',@bh OUT
SELECT @bh
--结果: CG0402020001
GO

2.4 使用编号表生成流水号的示例

--编号表
CREATE TABLE tb_NO(
Name char(2) PRIMARY KEY, --编号种类的名称
Head nvarchar(10) NOT NULL DEFAULT '', --编号的前缀
CurrentNo int NOT NULL DEFAULT 0, --当前编号
BHLen int NOT NULL DEFAULT 6, --编号数字部分长度
DESCRIPTION NVARCHAR(50)) --编号种类说明

INSERT tb_NO SELECT 'CG','CG',0,4,N'采购订单'
UNION ALL SELECT 'CJ','CJ',0,4,N'采购进货'
UNION ALL SELECT 'JC','JC',0,4,N'进仓单'
UNION ALL SELECT 'ZC','ZC',0,4,N'转仓单'
UNION ALL SELECT 'CC','CC',0,4,N'出仓单'
GO

--获取新编号的存储过程
CREATE PROC p_NextBH
@Name char(2), --编号种类
@BH nvarchar(20) OUTPUT --新编号
AS
BEGIN TRAN
   UPDATE tb_NO WITH(ROWLOCK) SET
       @BH=Head+RIGHT(POWER(10,BHLen)+CurrentNo+1,BHLen),
       CurrentNo=CurrentNo+1
   WHERE Name=@Name
COMMIT TRAN
GO

--获取 CJ 的新编号
DECLARE @bh char(6)
EXEC p_NextBH 'CJ',@bh OUT
SELECT @bh
--结果: CJ0001

EXEC p_NextBH 'CJ',@bh OUT
SELECT @bh
--结果: CJ0002
GO

--获取 CC 的新编号
DECLARE @bh char(6)
EXEC p_NextBH 'CC',@bh OUT
SELECT @bh
--结果: CC0001

EXEC p_NextBH 'CC',@bh OUT
SELECT @bh
--结果: CC0002

2.5生成纯数字随机编号的示例

--取得随机数的视图
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO

--生成随机编号的函数
CREATE FUNCTION f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
   DECLARE @r varchar(50)
   IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
       SET @BHLen=10

lb_bh:   --生成随机编号的处理
   SELECT @r=re FROM v_RAND
   WHILE LEN(@r)<@BHLen
       SELECT @r=@r+re FROM v_RAND
   SET @r=LEFT(@r,@BHLen)

   --检查编号在基础数据表中是否存在
   IF EXISTS(SELECT * FROM tb WITH(XLOCK,PAGLOCK) WHERE BH=@r)
       GOTO lb_bh

RETURN(@r)
END
GO

--创建引用生成随机编号的函数
CREATE TABLE tb(
BH char(10) PRIMARY KEY DEFAULT dbo.f_RANDBH(10),
col int)

--插放数据
BEGIN TRAN
   INSERT tb(col) VALUES(1)
   INSERT tb(col) VALUES(2)
   INSERT tb(col) VALUES(3)
COMMIT TRAN
SELECT * FROM tb
GO
/*--结果
BH col
------------------------------ -----------
6128177354 1
7378536177 3
8387186129 2
--*/

2.6 生成纯字母随机编号的示例(大小写混合)

--取得随机数的视图
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO

--生成随机编号的函数
CREATE FUNCTION f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
   DECLARE @r varchar(50)
   IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
       SET @BHLen=10

   SELECT @r=CHAR(
           CASE WHEN SUBSTRING(re,1,1)>5 THEN 97 ELSE 65 end
           +(SUBSTRING(re,1,1)
           +SUBSTRING(re,2,1)
           +SUBSTRING(re,3,1))%26)
       +CHAR(
           CASE WHEN SUBSTRING(re,4,1)>5 THEN 97 ELSE 65 end
           +(SUBSTRING(re,4,1)
           +SUBSTRING(re,5,1)
           +SUBSTRING(re,6,1))%26)
   FROM v_RAND
   WHILE LEN(@r)<@BHLen
       SELECT @r=@r+CHAR(
               CASE WHEN SUBSTRING(re,1,1)>5 THEN 97 ELSE 65 end
               +(SUBSTRING(re,1,1)
               +SUBSTRING(re,2,1)
               +SUBSTRING(re,3,1))%26)
           +CHAR(
               CASE WHEN SUBSTRING(re,4,1)>5 THEN 97 ELSE 65 end
               +(SUBSTRING(re,4,1)
               +SUBSTRING(re,5,1)
               +SUBSTRING(re,6,1))%26)
       FROM v_RAND
   RETURN(LEFT(@r,@BHLen))
END
GO

--调用
SELECT dbo.f_RANDBH(6),dbo.f_RANDBH(8)
--结果: YZVBOj LASCrhSO

2.7 生成纯字母随机编号的示例(仅大小或者小写)

--取得随机数的视图
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO

--生成随机编号的函数
CREATE FUNCTION f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
   DECLARE @r varchar(50)
   IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
       SET @BHLen=10

   SELECT @r=CHAR(65
           +(SUBSTRING(re,1,1)
           +SUBSTRING(re,2,1)
           +SUBSTRING(re,3,1))%26)
       +CHAR(65
           +(SUBSTRING(re,4,1)
           +SUBSTRING(re,5,1)
           +SUBSTRING(re,6,1))%26)
   FROM v_RAND
   WHILE LEN(@r)<@BHLen
       SELECT @r=@r+CHAR(65
               +(SUBSTRING(re,1,1)
               +SUBSTRING(re,2,1)
               +SUBSTRING(re,3,1))%26)
           +CHAR(65
               +(SUBSTRING(re,4,1)
               +SUBSTRING(re,5,1)
               +SUBSTRING(re,6,1))%26)
       FROM v_RAND
   RETURN(LEFT(@r,@BHLen))
END
GO

--调用
SELECT dbo.f_RANDBH(6),dbo.f_RANDBH(8)
--结果: UJXIJD PAPGTQUX

3.1 融合了补号处理的编号生成处理示例

--1．融合了补号处理的流水号编号处理
--下面是使用补号法生成流水编号的用户定义函数，生成的编号总长度为8位，以BH开头，其余6位为流水号。

--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
   DECLARE @r char(8)

   SELECT @r='BH'+RIGHT(1000001+MIN(BH),6)
   FROM(
       SELECT BH=RIGHT(BH,6) FROM tb WITH(XLOCK,PAGLOCK)
       UNION ALL SELECT 0
   )a WHERE NOT EXISTS(
       SELECT * FROM tb WITH(XLOCK,PAGLOCK)
       WHERE BH='BH'+RIGHT(1000001+a.BH,6))
   RETURN(@r)
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
GO

/*==================================================*/

--2．融合了补号处理的日期编号处理
--下面是使用补号法生成日期编号的用户定义函数，生成的编号总长度为12，前6位为日期信息，格式为YYMMDD，后6位为流水号。

--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12)
GO

--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6),@r char(12)
SELECT @dt=dt FROM v_GetDate

   SELECT @r=@dt+RIGHT(1000001+MIN(BH),6)
   FROM(
       SELECT BH=RIGHT(BH,6) FROM tb WITH(XLOCK,PAGLOCK)
       WHERE BH like @dt+'%'
       UNION ALL SELECT 0
   )a WHERE NOT EXISTS(
       SELECT * FROM tb WITH(XLOCK,PAGLOCK)
       WHERE BH like @dt+'%'
           AND BH=@dt+RIGHT(1000001+a.BH,6))
   RETURN(@r)
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

3.2 名次查询的处理示例

--示例数据
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO

--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 6
--*/

--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8
--*/

3.3 使用UPDATE进行编号重排的处理示例

--测试资料
CREATE TABLE tb(
ID1 char(2) NOT NULL,
ID2 char(4) NOT NULL,
col int,
PRIMARY KEY(ID1,ID2))
INSERT tb SELECT 'aa','0001',1
UNION ALL SELECT 'aa','0003',2
UNION ALL SELECT 'aa','0004',3
UNION ALL SELECT 'bb','0005',4
UNION ALL SELECT 'bb','0006',5
UNION ALL SELECT 'cc','0007',6
UNION ALL SELECT 'cc','0009',7
GO

--重排编号处理
DECLARE @ID1 char(2),@ID2 int
UPDATE tb SET
@ID2=CASE WHEN @ID1=ID1 THEN @ID2+1 ELSE 10001 END,
@ID1=ID1,ID2=RIGHT(@ID2,4)
SELECT * FROM tb
/*--结果
ID1 ID2 col
---- ---- -----------
aa 0001 1
aa 0002 2
aa 0003 3
bb 0001 4
bb 0002 5
cc 0001 6
cc 0002 7
--*/

3.4 使用临时表进行编号重排的处理示例

--重排编号处理
SELECT ID=IDENTITY(int,0,1),* INTO # FROM tb ORDER BY ID1,ID2
UPDATE a SET ID2=RIGHT(10001+b1.ID-b2.ID,4)
FROM tb a,# b1,(SELECT ID1,ID=MIN(ID) FROM # GROUP BY ID1)b2
WHERE a.ID1=b1.ID1 AND a.ID2=b1.ID2
AND b1.ID1=b2.ID1
DROP TABLE #
SELECT * FROM tb
/*--结果
ID1 ID2 col
---- ---- -----------
aa 0001 1
aa 0002 2
aa 0003 3
bb 0001 4
bb 0002 5
cc 0001 6
cc 0002 7
--*/

3.5 使用子查询进行编号重排的处理示例

--重排编号处理
DECLARE @ID1 char(2),@ID2 int
UPDATE a SET ID2=RIGHT(10000
+(SELECT COUNT(*) FROM tb WHERE ID1=a.ID1 AND ID2<=a.ID2)
,4)
FROM tb a
SELECT * FROM tb
/*--结果
ID1 ID2 col
---- ---- -----------
aa 0001 1
aa 0002 2
aa 0003 3
bb 0001 4
bb 0002 5
cc 0001 6
cc 0002 7
--*/

4.1 查询已用编号分布情况的示例（临时表法)

--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',3
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--已用编号分布查询
SELECT id=IDENTITY(int),col1,col2 INTO #1 FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2-1)
SELECT id=IDENTITY(int),col2 INTO #2 FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
SELECT a.col1,start_col2=a.col2,end_col2=b.col2
FROM #1 a,#2 b
WHERE a.id=b.id
DROP TABLE #1,#2
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 2 3
a 6 8
b 3 3
b 5 7
--*/

4.2 查询已用编号分布情况的示例（子查询法)

--已用编号分布查询
SELECT col1,start_col2=col2,
   end_col2=(
       SELECT MIN(col2) FROM tb aa
       WHERE col1=a.col1 AND col2>=a.col2
           AND NOT EXISTS(
               SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2+1))
FROM tb a
WHERE NOT EXISTS(
   SELECT * FROM tb WHERE col1=a.col1 and col2=a.col2-1)
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 2 3
a 6 8
b 3 3
b 5 7
--*/

4.3 查询缺号分布情况的示例

--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
   end_col2=(
       SELECT MIN(col2) FROM tb aa
       WHERE col1=a.col1 AND col2>a.col2
           AND NOT EXISTS(
               SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
       -1
FROM(
   SELECT col1,col2 FROM tb
   UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
   SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
   AND NOT EXISTS(
       SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 1 1
a 4 5
b 2 4
--*/

4.4 返回已用编号、缺号分布字符串的处理示例

--生成已用编号分布字符串的函数
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @re varchar(8000),@pid int
   SELECT @re='',@pid=-1
   SELECT @re=CASE
           WHEN col2=@pid+1 THEN @re
           ELSE @re
               +CASE
                   WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
                   ELSE CAST(-@pid as varchar)
               END
               +','+CAST(col2 as varchar)
           END,
       @pid=col2
   FROM tb
   WHERE col1=@col1
   ORDER BY col2
   RETURN(STUFF(@re,1,2,'')
       +CASE
           WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
           ELSE CAST(-@pid as varchar)
       END)
END
GO

--生成缺号分布字符串的函数
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
   DECLARE @re varchar(8000),@pid int
   SELECT @re='',@pid=0
   SELECT @re=CASE
           WHEN col2=@pid+1 THEN @re
           ELSE @re+','+CAST(@pid+1 as varchar)
               +CASE
                   WHEN @pid+1=col2-1 THEN ''
                   ELSE CAST(1-col2 as varchar)
               END
           END,
       @pid=col2
   FROM tb
   WHERE col1=@col1
   ORDER BY col2
   RETURN(STUFF(@re,1,1,''))
END
GO

--调用测试
--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',5
UNION ALL SELECT 'a',8
UNION ALL SELECT 'a',9
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7

--查询
SELECT col1,
col2_Series=dbo.f_GetStrSeries(col1),
col2_Series=dbo.f_GetStrNSeries(col1)
FROM tb
GROUP BY col1
/*--结果
col1 col2_Series col2_Series
-------------- ------------------------ --------------
a 2-3,5,8-9 1,4,6-7
b 1,5-7 2-4
--*/

4.5 缺勤天数统计的处理示例

--计算两个日期之间相差的工作天数
CREATE FUNCTION f_WorkDateDiff(
@dt_begin datetime,
@dt_end datetime)
RETURNS int
AS
BEGIN
   DECLARE @workday int,@i int,@bz bit,@dt datetime
   IF @dt_begin>@dt_end
       SELECT @bz=1,@dt=@dt_bsegin,@dt_begin=@dt_end,@dt_end=@dt
   ELSE
       SET @bz=0
   SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
       @workday=@i/7*5,
       @dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
   WHILE @dt_begin<=@dt_end
   BEGIN
       SELECT @workday=CASE
           WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
           THEN @workday+1 ELSE @workday END,
           @dt_begin=@dt_begin+1
   END
   RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO

--测试数据
CREATE TABLE tb(Name varchar(10),WorkDate datetime)
INSERT tb SELECT 'aa','2005-01-03'
UNION ALL SELECT 'aa','2005-01-04'
UNION ALL SELECT 'aa','2005-01-05'
UNION ALL SELECT 'aa','2005-01-06'
UNION ALL SELECT 'aa','2005-01-07'
UNION ALL SELECT 'aa','2005-01-10'
UNION ALL SELECT 'aa','2005-01-14'
UNION ALL SELECT 'aa','2005-01-17'
UNION ALL SELECT 'bb','2005-01-11'
UNION ALL SELECT 'bb','2005-01-12'
UNION ALL SELECT 'bb','2005-01-13'
UNION ALL SELECT 'bb','2005-01-10'
UNION ALL SELECT 'bb','2005-01-14'
UNION ALL SELECT 'bb','2005-01-20'
GO

--缺勤统计
DECLARE @dt_begin datetime,@dt_end datetime
SELECT @dt_begin='2005-1-1', --统计的开始日期
@dt_end='2005-1-20' --统计的结束日期

--统计
SELECT Name,Days=SUM(Days) FROM(
   SELECT Name,Days=dbo.f_WorkDateDiff(
           DATEADD(Day,1,WorkDate),
           ISNULL(DATEADD(Day,-1,(
               SELECT MIN(WorkDate) FROM tb aa
               WHERE Name=a.Name
                   AND WorkDate>a.WorkDate AND WorkDate<=@dt_end
                   AND NOT EXISTS(
                       SELECT * FROM tb
                       WHERE WorkDate>@dt_begin AND WorkDate<=@dt_end
                           AND Name=aa.Name
                           AND dbo.f_WorkDateDiff(WorkDate,aa.WorkDate)=2))
               ),@dt_end))
   FROM(
       SELECT Name,WorkDate FROM tb
       WHERE WorkDate>=@dt_begin AND WorkDate<@dt_end
       UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
       SELECT DISTINCT Name,DATEADD(Day,-1,@dt_begin) FROM tb
   )a
   WHERE (@@DATEFIRST+DATEPART(Weekday,WorkDate)-1)%7 BETWEEN 1 AND 5
       AND NOT EXISTS(
           SELECT * FROM tb
           WHERE WorkDate>@dt_begin AND WorkDate<=@dt_end
               AND Name=a.Name
               AND dbo.f_WorkDateDiff(WorkDate,a.WorkDate)=-2)
)aa GROUP BY Name
/*--结果
Name Days
---------------- -----------
aa 6
bb 8
--*/

5.1 -补位法

--自已做标识列的例子,不自动重排编号,而是自动补号:

--创建得到最大id的函数
create function f_getid()
returns char(3)
as
begin
declare @id int

if not exists(select 1 from tb where id='001')
   set @id=1
else
begin
   select @id=max(id) from tb
   if @id is null
       set @id=1
   else
   begin
       declare @id1 int
       select @id1=min(id) from tb a where id<>@id and not exists(select 1 from tb where id=a.id+1)
       if @id1 is not null set @id=@id1
       set @id=@id+1
   end
end

lb_re:
return(right('000'+cast(@id as varchar),3))
end
go

--创建表
create table tb(id char(3) primary key default dbo.f_getid(),name varchar(10))
go

--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')

--显示插入的结果
select * from tb

--删除部分记录
delete from tb where name in('张三','张七','张八','张十')

--显示删除后的结果
select * from tb

--再次插入记录
insert into tb(name) values('李一')
insert into tb(name) values('李二')

--显示插入的结果
select * from tb order by id
go

--删除环境
drop table tb
drop function f_getid

/*--测试结果
id name
---- ----------
001 李一
002 张四
003 张五
004 张六
005 李二
007 张九

（所影响的行数为 6 行）
--*/

5.2 材料流水号

--自动编号的例子.材料编号=类别编号+流水号

--创建自定义函数,得到新的ID
create function f_getid(
@类别编号 varchar(3))
returns int
as
begin
   declare @re int
   select @re=right(id,4) from(
       select id=max(材料编号) from tb where 类别编号=@类别编号
   ) a
   set @re=isnull(@re,0)+1
   return(@re)
end
go

--创建测试表
create table tb(材料编号 varchar(7) primary key default '',类别编号 varchar(3),材料名称 varchar(10))
go

--创建触发器,自动生成材料编号
create trigger t_insert on tb
instead of insert
as
select * into #t from inserted order by 类别编号
declare @类别编号 varchar(3),@id int
update #t set @id=case when @类别编号=类别编号 then @id+1 else dbo.f_getid(类别编号) end
,材料编号=类别编号+right('0000'+cast(@id as varchar),4)
,@类别编号=类别编号
insert into tb select * from #t
go

--插入数据测试
insert into tb(类别编号,材料名称)
select '101','A材料'
union all select '101','B材料'
union all select '302','C材料'

--显示结果
select * from tb order by 材料编号

go
--删除测试环境
drop table tb
drop function f_getid

5.3 触发器自动维护已用&未用编号

--根据 table2 表中的记录变化情况,自动修改 table1 表的内容

--示例

--示例数据
create table table1(序号 int,类别 varchar(10),起始号 char(7),终止号 char(7),总数 int,已用票号 varchar(8000),已用票数 int,结余票数 int,结余票号 varchar(8000),组合编号 varchar(20))
insert table1 select 1,'A','0000001','0000010',10,NULL,0,10,'0000001-0000010','A-0000001-0000010'
union all select 2,'B','0000011','0000020',10,NULL,0,10,'0000011-0000020','B-0000011-0000020'

create table table2(组合编号 varchar(20),类别 varchar(10),票号 char(7))
go

--触发器
create trigger tr_process on table2
for insert,update,delete
as
select id=identity(int,1,1)
   ,a.组合编号,a.票号
   ,b.起始号,b.终止号
   ,已用票号=cast(null as [varchar] (8000))
   ,结余票号=cast(null as [varchar] (8000))
into #t
from table2 a,table1 b
where a.组合编号=b.组合编号
   and (exists(select 1 from inserted where 组合编号=a.组合编号)
       or exists(select 1 from deleted where 组合编号=a.组合编号))
order by a.组合编号,a.票号

declare @组合编号 varchar(20),@票号 int
,@已用票号 varchar(8000),@结余票号 varchar(8000)

update #t set
   @已用票号=case
       when 组合编号=@组合编号
       then case
           when 票号=@票号+1
           then case
               when right(@已用票号,1)='-'
               then @已用票号+票号
               else left(@已用票号,len(@已用票号)-7)+票号
               end
           else case
               when right(@已用票号,1)='-'
               then left(@已用票号,len(@已用票号)-1)
               else @已用票号 end+','+票号+'-'
               end
       else 票号+'-'
       end,

   @结余票号=case
       when 组合编号=@组合编号
       then case
           when 票号=@票号+1
           then left(@结余票号,len(@结余票号)-8)
           when right(9999999+票号,7)+'-'=right(@结余票号,8)
           then left(@结余票号,len(@结余票号)-1)+','
           else @结余票号+right(9999999+票号,7)+','
           end+right(10000001+票号,7)+'-'
       else case
           when 起始号=票号
           then ''
           when cast(起始号 as int)+1=票号
           then 起始号+','
           else 起始号+'-'+right(9999999+票号,7)+','
           end+right(10000001+票号,7)+'-'
       end,
   已用票号=@已用票号,
   结余票号=@结余票号,
   @票号=票号,
   @组合编号=组合编号

update a set
   已用票号=case
       when right(b.已用票号,1)='-'
       then left(b.已用票号,len(b.已用票号)-1)
       else b.已用票号
       end,
   结余票号=case
       when b.终止号=b.票号
       then left(b.结余票号,len(b.结余票号)-1)
       else b.结余票号+b.终止号
       end,
   已用票数=c.已用票数,
   结余票数=a.总数-c.已用票数
from table1 a,#t b,(
   select id=max(id),已用票数=count(*)
   from #t
   group by 组合编号
)c where a.组合编号=b.组合编号
   and b.id=c.id

--处理在子表中被全部删除的数据
if exists(select 1 from deleted a where not exists(select 1 from table2 where 组合编号=a.组合编号))
   update a set 已用票号='',已用票数=0,结余票数=a.总数,结余票号=a.起始号+'-'+a.终止号
   from table1 a,(
       select distinct 组合编号 from deleted a
       where not exists(select 1 from table2 where 组合编号=a.组合编号)
   )b where a.组合编号=b.组合编号
go

--插入第1条记录
insert table2 select 'A-0000001-0000010','A','0000001'