SQL Server 学习5(随机数,整数,编号,序号)

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 使用临时表进行编号重排的处理示例

--测试资料
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

--重排编号处理
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 使用子查询进行编号重排的处理示例

--测试资料
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 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 查询已用编号分布情况的示例(子查询法)

--测试数据
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 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'

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

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

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

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

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

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

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--修改记录
update table2 set 组合编号='B-0000011-0000020',票号='0000011'
where 组合编号='A-0000001-0000010' and 票号='0000002'

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--批量删除:
delete from table2 
where 票号 in ('0000001','0000002','0000011')

--显示结果
select 序号,已用票号,已用票数,结余票数,结余票号,组合编号 from table1
go

--删除测试
drop table table1,table2

/*--结果自己看--*/

5.4 防止重复的示例

--自己做编号,防止冲突的处理

--处理示例1(用主键/唯一键)

--得到最新编号的函数
create function f_newid()
returns char(7)    --编号位数固定,用char的检索效率高于varchar
as
begin
    declare @re char(7)
    select @re=max(BHID) from 表 
    return(
        case when @re is null then 'BH00001'
            else 'BH'+right('0000'+cast(cast(right(@re,5) as int)+1 as varchar),5) 
        end)
end
go

--测试的表,表名与函数中的表名对应
create table 表(
BHID char(7) default dbo.f_newid()    --设置默认值,自动生成编号
    primary key,  --设置成主键,防止编号冲突
--    constraint UNIQUE_BHID_表 unique,    --如果用唯一约束,则删除上面的主键约束语句,改用此句
txt1 varchar(10),
txt2 varchar(10),
num  float)
go

/*--插入数据时,就可以不理会编号字段,直接用这样的语句
    多用户同时插入时,如果编号重复,就会有错误发生
    此时,前台程序拦截错误,如果是违反约束的错误
    只需要重新执行插入的语句即可,此时的编号会自动再重新生成
    而重新执行插入语句也很方便,因为根本就不需要改语句
--*/
insert 表(txt1,txt2,num) values('aa','bb',1)

go
--删除测试
drop table 表
drop function f_newid


--处理示例2(用表级锁)

--得到最新编号的函数
create function f_newid()
returns char(7)    --编号位数固定,用char的检索效率高于varchar
as
begin
    declare @re char(7)
    select @re=max(BHID) from 表(tablockx) --加表级锁
    return(
        case when @re is null then 'BH00001'
            else 'BH'+right('0000'+cast(cast(right(@re,5) as int)+1 as varchar),5) 
        end)
end
go

--测试的表,表名与函数中的表名对应
create table 表(
BHID char(7) default dbo.f_newid()    --设置默认值,自动生成编号
txt1 varchar(10),
txt2 varchar(10),
num  float)
go

--用事务/配合函数中的锁,就可以实现在插入时锁定表
begin tran
insert 表(txt1,txt2,num) values('aa','bb',1)
commit tran

go
--删除测试
drop table 表
drop function f_newid

/*--关于两种方法:

设置主键或唯一键的处理效率是最高的,因为在处理数据的同时,其他用户还可以浏览数据,

如果不用主键或唯一键,就要用表级锁,而用表级锁,在锁定时,其他用户是不能做任何操作的,这样在多用户环境下,进程肯定容易堵塞,造成千军万马过独木桥的情况.
--*/

5.5 关联部门流水号

/*--自己做编号的示例:
    根据输入的RoleID,另一个表中查到一个部门ID(三位)
    然后用部门ID作为插入记录流水号的前三位,后面六位为自动增加的。
--*/

--测试环境

--部门表
create table 部门(部门id char(3),部门名称 varchar(10),RoleID int)
insert 部门 
select '001','A部门',1
union all select '002','B部门',2
union all select '003','c部门',3

--A表
create table A表(编号 char(9) primary key default '',RoleID int)
go

--处理的函数
create function f_getid(@RoleID int)
returns char(9)
as
begin
    declare @re char(9),@部门id char(3)

    select @部门id=部门id from 部门 where RoleID=@RoleID
    select @re=max(编号) from A表
    where 编号 like @部门id+'%'
    return(@部门id+case when @re is null then '000001' 
        else right('000000'+cast(cast(right(@re,6) as int)+1 as varchar),6) end)
end
go

--创建触发器,自动生成编号
create trigger t_insert on A表
instead of insert
as
declare @部门编号 char(3),@id int,@RoleID int,@编号 char(9)

select * into #t from inserted order by RoleID

update #t set 
    @编号=case RoleID when @RoleID then @编号 else dbo.f_getid(RoleID) end
    ,@部门编号=case RoleID when @RoleID then @部门编号 else left(@编号,3) end
    ,@id=case RoleID when @RoleID then @id+1 else right(@编号,6) end
    ,编号=@部门编号+right('000000'+cast(@id as varchar),6)
    ,@RoleID=RoleID
insert into A表 select * from #t
go

--插入数据到A表
insert A表(RoleID)
select 1
union all select 1
union all select 2
union all select 3
union all select 2
union all select 1
union all select 2
union all select 3
union all select 3
go

--显示处理结果
select * from A表
go

--删除测试环境
drop table 部门,A表
drop function f_getid

/*--测试结果

编号        RoleID      
--------- ----------- 
001000001 1
001000002 1
001000003 1
002000001 2
002000002 2
002000003 2
003000001 3
003000002 3
003000003 3

(所影响的行数为 9 行)
--*/

    

5.6 开票统计--涉及到连号处理

/*
    开票统计--涉及到连号处理
*/
create table #tb(th int,time datetime,userintime datetime,outtime datetime
    ,userid varchar(4),ph int)
insert into #tb
    select 2,'2002-8-24 8:15:07','8:15:07',null,'4002',Null    
    union all select 2,'2002-8-24 8:58:21',null,null,'4002',1020001
    union all select 2,'2002-8-24 9:01:53',null,null,'4002',1020002
    union all select 2,'2002-8-24 9:02:35',null,null,'4002',1020003
    union all select 2,'2002-8-24 9:03:01',null,null,'4002',1020004
    union all select 2,'2002-8-24 16:04:35',null,null,'4002',1020353
    union all select 2,'2002-8-24 16:05:26',null,'16:05:26','4002',Null
    union all select 2,'2002-8-25 8:15:07','8:15:07',null,'4002',Null    
    union all select 2,'2002-8-25 8:58:21',null,null,'4002',1020337
    union all select 2,'2002-8-25 9:01:53',null,null,'4002',1020339
    union all select 2,'2002-8-25 9:02:35',null,null,'4002',1020340
    union all select 2,'2002-8-25 9:03:01',null,'9:03:01','4002',null
    union all select 2,'2002-8-25 9:03:01','9:03:01',null,'4002',null
    union all select 3,'2002-8-25 16:04:35',null,null,'4002',1020353
    union all select 2,'2002-8-25 16:05:26',null,'16:05:26','4002',Null
    union all select 2,'2002-8-25 8:15:07','8:15:07',null,'4003',Null    
    union all select 2,'2002-8-25 8:58:21',null,null,'4003',1020337
    union all select 2,'2002-8-25 9:01:53',null,null,'4003',1020339
    union all select 2,'2002-8-25 9:02:35',null,null,'4003',1020340
    union all select 2,'2002-8-25 9:03:01',null,null,'4003',1020344
    union all select 2,'2002-8-25 16:04:35',null,null,'4003',1020353
    union all select 2,'2002-8-25 16:05:26',null,'16:05:26','4003',Null

--select * from #tb

/* 仅显示开票情况,不包括上下班记录的处理
--连号开始编号
select id=identity(int,1,1),th,time,userid,ph
    into #temp1
    from #tb a 
    where ph is not null and  -- userid='4002' and --如果只需要指定用户,就加上此条件
        not exists(select 1 from #tb where th=a.th and userid=a.userid and ph=a.ph-1)
--select * from #temp1

--连号结束编号
select id=identity(int,1,1),th,time,userid,ph
    into #temp2
    from #tb a 
    where ph is not null and -- userid='4002' and --如果只需要指定用户,就加上此条件
        not exists(select 1 from #tb where th=a.th and userid=a.userid and ph=a.ph+1)
--select * from #temp2
--*/


--/* 包括上下班记录的处理
--连号开始编号
select id=identity(int,1,1),th,time,userid
    ,isnull(cast(ph as varchar),case when outtime is null then '上班' else '下班' end) as ph
    into #temp1
    from #tb a 
    where -- userid='4002' and --如果只需要指定用户,就加上此条件
        not exists(select 1 from #tb where th=a.th and userid=a.userid and ph=a.ph-1)
--select * from #temp1

--连号结束编号
select id=identity(int,1,1),th,time,userid
    ,isnull(cast(ph as varchar),convert(varchar,time,108)) as ph
    into #temp2
    from #tb a 
    where -- userid='4002' and --如果只需要指定用户,就加上此条件
        not exists(select 1 from #tb where th=a.th and userid=a.userid and ph=a.ph+1)
--select * from #temp1
--*/

--得到结果
select a.th,a.time,a.userid
    ,cast(a.ph as varchar)+'--'+cast(b.ph as varchar) as 开票区间
    --,a.ph as 开始编号,b.ph as 结束编号
    from #temp1 a,#temp2 b
    where a.id=b.id

drop table #tb,#temp1,#temp2

 

5.7 类别自动生成编号示例

--根据输入类别自动生成编号示例

--表
create table tab(a varchar(20),b varchar(100))
go

--触发器
create trigger tr_insert on tab
instead of insert
as
declare @dt varchar(10)
set @dt='-'+convert(varchar(10),getdate(),120)

select * into #t 
from inserted a join(
    select gid_new=b.a,sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0)
    from tab a 
        right join inserted b on charindex(b.a+@dt,a.a)=1
    group by b.a
)b on a.a=b.gid_new
order by b.gid_new

declare @nid varchar(100),@a int
update #t set @a=case @nid when gid_new then @a+1 else sid_new end
    ,a=gid_new+@dt+'-'+right(@a,5)
    ,@nid=gid_new

insert tab select a,b from #t
go

--插入数据
insert tab values('sj','2222')
insert tab  select 'sj','324324'
union  all  select 'sj','33343'
union  all  select 'dj','33343'
union  all  select 'dj','24324'
union  all  select 'sj','24234'

--显示插入结果
select * from tab order by a
go

--删除测试
drop table tab

/*--测试结果

a                    b       
-------------------- --------
dj-2004-07-1-00001   33343
dj-2004-07-1-00002   24324
sj-2004-07-1-00001   2222
sj-2004-07-1-00002   324324
sj-2004-07-1-00003   33343
sj-2004-07-1-00004   24234

(所影响的行数为 6 行)
--*/

5.8 连续编号

select id=a.id+b.id+c.id+d.id
from(
    select id=0 union all select 1
    union all select id=2 union all select 3
    union all select id=4 union all select 5
    union all select id=6 union all select 7
    union all select id=8 union all select 9
) a,(
    select id=0 union all select 10
    union all select id=20 union all select 30
    union all select id=40 union all select 50
    union all select id=60 union all select 70
    union all select id=80 union all select 90
) b,(
    select id=0 union all select 100
    union all select id=200 union all select 300
    union all select id=400 union all select 500
    union all select id=600 union all select 700
    union all select id=800 union all select 900
) c,(
    select id=0 union all select 1000
    union all select id=2000 union all select 3000
    union all select id=4000 union all select 5000
    union all select id=6000 union all select 7000
    union all select id=8000 union all select 9000
) d
order by id

5.9 流水号

--生成流水号

--创建测试表
create table test(id varchar(18),  --流水号,日期(8位)+时间(4位)+流水号(4位)
    name varchar(10)  --其他字段
)

go
--创建生成流水号的触发器
create trigger t_insert on test
INSTEAD OF insert
as
declare @id varchar(18),@id1 int,@head varchar(12)
select * into #tb from inserted
set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','')
select @id=max(id) from test where id like @head+'%'
if @id is null
    set @id1=0
else
    set @id1=cast(substring(@id,13,4) as int)
update #tb set @id1=@id1+1
    ,id=@head+right('0000'+cast(@id1 as varchar),4)
insert into test select * from #tb
go


--插入数据,进行测试
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'

--修改系统时间,再插入数据测试一次
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'

--显示测试结果
select * from test


--删除测试环境
drop table test

/*--测试结果
id                 name       
------------------ ---------- 
2004022720430001   aa
2004022720430002   bb
2004022720430003   cc
2004022720430004   aa
2004022720430005   bb
2004022720430006   cc

(所影响的行数为 6 行)
--*/

5.10 日期流水号

--自已做标识列的例子--流水号:日期+当日编号:

--创建视图,得到当前日期(因为函数中不能使用getdate())
create view v_getdate as select dt=convert(varchar,getdate(),112)
go

--创建得到最大id的函数
create function f_getid()
returns varchar(12)
as
begin
    declare @id varchar(12),@dt varchar(8)
    select @dt=dt from v_getdate
    select @id=@dt+'-'+right(1001+isnull(right(max(id),3),0),3)
    from tb where id like @dt+'-%'
    return(@id)
end
go

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


/*--创建触发器,在删除表中的记录时,自动更新记录的id
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
--*/
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

go
--删除环境
drop table tb
drop view v_getdate
drop function f_getid

5.11 十六进制

--创建自定义函数,得到新的ID
create function f_getid()
returns varchar(12)
as
begin
    declare @id varchar(12)
    declare @st varchar(16),@id1 varchar(1),@id2 varchar(1),@i int

    set @st='0123456789ABCDEF'
    select @id=max(主键) from tb

    if @id is null 
        set @id='03000001-P01'
    else
        select @id1=substring(@id,11,1),@id2=right(@id,1)
            ,@id=left(@id,10)
            ,@i=charindex(@id2,@st)
            ,@id2=case @i when 16 then '0' else substring(@st,@i+1,1) end
            ,@id1=case @i when 16 then substring(@st,charindex(@id1,@st)+1,1)
                else @id1 end
            ,@id=@id+@id1+@id2
return(@id)
end
go

--创建测试表
create table tb(主键 varchar(12) not null primary key default dbo.f_getid()
        ,aa int)

--插入数据测试
insert into tb(aa) values(1)
insert into tb(aa) values(2)
insert into tb(aa) values(3)
insert into tb(aa) values(4)
insert into tb(aa) values(5)
insert into tb(aa) values(6)
insert into tb(aa) values(7)
insert into tb(aa) values(8)
insert into tb(aa) values(9)
insert into tb(aa) values(10)
insert into tb(aa) values(11)
insert into tb(aa) values(12)
insert into tb(aa) values(13)
insert into tb(aa) values(14)
insert into tb(aa) values(15)
insert into tb(aa) values(16)
insert into tb(aa) values(17)
insert into tb(aa) values(18)

--显示结果
select * from tb

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

5.12 箱编号连号处理

/*
我现在在做一个包装方面的程序,数据表table是这样设计的:
产品号,产品名,箱号,箱如仓时,就记录箱的编号, 
现在要求做如下报表:如果A产品已经装了1,2,3,4,5,6,7,8,9号箱,则显示:

产品号    产品名    已装箱数     箱编排
==================================================
A    AAA    9        1~9

假如第7号箱没入仓,则显示:

产品号    产品名    已装箱数     箱编排
==================================================
A    AAA    6        1~6
A    AAA    2        8~9

我想用一个视图(sql 2000)来实现,请教要如果实现


*/
--创建数据测试环境
create table 数据表(产品号 varchar(1),产品名 varchar(10),箱号 int,箱如仓时 datetime,就记录箱的编号 int)
insert into 数据表
    select 'A','AAA',1,null,null
    union all select 'A','AAA',2,null,null
    union all select 'A','AAA',3,null,null
    union all select 'A','AAA',4,null,null
    union all select 'B','AAA',5,null,null
    union all select 'B','AAA',6,null,null
    union all select 'A','AAA',7,null,null
    union all select 'A','AAA',8,null,null
    union all select 'B','AAA',9,null,null
    union all select 'C','AAA',10,null,null
go

--创建自定义函数,得到箱号连续表
create function f_getseriesbh()
returns @re table(产品号 varchar(1),开始箱号 int,结束箱号 int)
as
begin
    declare @tb1 table(id int identity(1,1),产品号 varchar(1),箱号 int)
    declare @tb2 table(id int identity(1,1),产品号 varchar(1),箱号 int)

    insert into @tb1(产品号,箱号) select 产品号,箱号
        from 数据表 a
        where not exists(select 1 from 数据表 where 产品号=a.产品号 and 箱号=a.箱号-1)
        order by 产品号,箱号

    insert into @tb2(产品号,箱号) select 产品号,箱号
        from 数据表 a
        where not exists(select 1 from 数据表 where 产品号=a.产品号 and 箱号=a.箱号+1)
        order by 产品号,箱号

    insert into @re
    select a.产品号,a.箱号,b.箱号 from @tb1 a inner join @tb2 b on a.id=b.id
    return
end
go

create view v_视图
as
select a.产品号,a.产品名
    ,已装箱数=b.结束箱号-b.开始箱号+1
    ,箱编排=cast(b.开始箱号 as varchar)+case b.结束箱号 when b.开始箱号 then '' else '~'+cast(b.结束箱号 as varchar) end
from (select distinct 产品号,产品名 from 数据表) a 
    inner join dbo.f_getseriesbh() b on a.产品号=b.产品号

go

select * from v_视图
go

drop table 数据表--,#tb
drop function f_getseriesbh
drop view v_视图
 

5.13 项目编号=各项目独立流水号&各年不同

--自动编号的例子.项目编号=各项目独立流水号/各年不同

--创建自定义函数,得到新的ID
create function f_getid(
@年份 char(4),
@项目名称 varchar(10)
)returns int
as
begin
    declare @re int

    select @re=case when id is null then 1 else cast(@re as int)+1 end
    from(
        select id=max(项目编号) from 表 where 年份=@年份 and 项目名称=@项目名称
    ) a
    return(@re)
end
go

--创建测试表
create table 表(年份 char(4),项目编号 char(4)
    ,项目名称 varchar(10),项目内容 varchar(100))
go

--创建触发器,自动生成项目编号
create trigger t_insert on 表
instead of insert
as
select * into #t from inserted order by 年份,项目名称
declare @年份 char(4),@项目名称 varchar(10),@id int

update #t set @id=case when @年份=年份 and @项目名称=项目名称
        then @id+1 else dbo.f_getid(年份,项目名称) end
    ,项目编号=right('0000'+cast(@id as varchar),4)
    ,@年份=年份,@项目名称=项目名称
insert into 表 select * from #t
go

--创建触发器,在删除/修改时,重新排序项目编号
create trigger t_delete_update on 表
after delete,update
as
declare @年份 char(4),@项目名称 varchar(10),@id int

declare tb cursor local for
    select 年份,项目名称 from deleted 
    union
    select 年份,项目名称 from inserted
open tb
fetch next from tb into @年份,@项目名称
while @@fetch_status=0
begin
    set @id=0
    update 表 set @id=@id+1
        ,项目编号=right('0000'+cast(@id as varchar),4)
    where 年份=@年份 and 项目名称=@项目名称
    fetch next from tb into @年份,@项目名称
end
close tb
deallocate tb
go

--插入数据测试
insert into 表(年份,项目名称,项目内容)
select '2003','A项目','A-1期'
union all select '2003','A项目','A-2期'
union all select '2004','A项目','A-3期'
union all select '2003','B项目','B-1期'
union all select '2004','B项目','B-11期'
union all select '2003','A项目','A-12期'
union all select '2004','A项目','A-12期'
union all select '2003','B项目','B-12期'
union all select '2004','B项目','B-13期'

--显示结果
select * from 表 order by 年份,项目名称
go

--更新测试
update 表 set 年份=2003 where 项目编号='0001' and 项目名称='A项目'

--显示结果
select * from 表 order by 年份,项目名称
go

--删除部分记录
delete from 表 
where (年份=2003 and 项目编号='0002') or (年份=2004 and 项目编号='0001')
--显示结果
select * from 表 order by 年份,项目名称

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

/*--测试结果

1.插入时,自动编号的效果

年份   项目编号 项目名称       项目内容  
---- ---- ---------- ------------------
2003 0001 A项目        A-1期
2003 0002 A项目        A-2期
2003 0003 A项目        A-12期
2003 0001 B项目        B-12期
2003 0002 B项目        B-1期
2004 0001 A项目        A-3期
2004 0002 A项目        A-12期
2004 0001 B项目        B-13期
2004 0002 B项目        B-11期


2.修改时,自动编号的效果

年份 项目编号 项目名称       项目内容  
---- ---- ---------- ------------------
2003 0001 A项目        A-1期
2003 0002 A项目        A-2期
2003 0003 A项目        A-12期
2003 0004 A项目        A-3期
2003 0001 B项目        B-12期
2003 0002 B项目        B-1期
2004 0001 A项目        A-12期
2004 0001 B项目        B-13期
2004 0002 B项目        B-11期


3.删除时,自动编号的效果

年份   项目编号 项目名称       项目内容 
---- ---- ---------- -----------------
2003 0001 A项目        A-1期
2003 0002 A项目        A-12期
2003 0003 A项目        A-3期
2003 0001 B项目        B-12期
2004 0001 B项目        B-11期
--*/

5.14 新编号查询示例(分类查询)

/*--查询新编号的示例

    要求:
    按id前4位分组,查询出最小一组的缺号,如果没有,则用最大组的id+1

--邹建 2004.12(引用请保留此信息)--*/

--测试数据
create table tb(id int)
insert tb select 10010001
union all select 10010002
union all select 10010003
union all select 10010004
union all select 10010005
--union all select 10020001
union all select 10020002
union all select 10020003
union all select 10020004
union all select 10030001
go

--查询处理1(最小编号为:xxxx0001的处理)
select 新id=left(id,4)+right(10001+right(a.id,4),4)
from(
    select id=isnull(min(a.id),(select max(id) from tb))
    from(
        select id from tb
        union all
        select distinct left(id,4)+'0000' from tb 
    )a left join(
        select id=max(id) from tb group by left(id,4)
    )b on a.id=b.id
    where b.id is null and     not exists(
        select * from tb where id=left(a.id,4)+right(10001+right(a.id,4),4))
)a

--查询处理2(最小编号为表中每组的最小编号)
select 新id=left(id,4)+right(10001+right(a.id,4),4)
from(
    select id=isnull(min(a.id),(select max(id) from tb))
    from tb a left join(
        select id=max(id) from tb group by left(id,4)
    )b on a.id=b.id
    where b.id is null and     not exists(
        select * from tb where id=left(a.id,4)+right(10001+right(a.id,4),4))
)a
go

--删除测试
drop table tb

/*--测试结果

新id              
---------------- 
10020001

(所影响的行数为 1 行)


新id              
---------------- 
10030002

(所影响的行数为 1 行)
--*/

5.15 新编号查询示例

/*--查询新编号的示例

    要求:
    查询出最小的缺号,如果没有,则用最大的id+1

--邹建 2004.12(引用请保留此信息)--*/

--测试数据
create table tb(id char(4))
insert tb select '0002'
--union all select '0001'
--union all select '0003'
--union all select '0004'
union all select '0005'
go

--查询处理1(最小编号为:0001的处理)
select 新id=right(10001+min(id),4)
from(
    select id from tb
    union all
    select '0000'
)a where not exists(
        select * from tb where id=right(10001+a.id,4))

--查询处理2(最小编号为表中现有数据的最小编号)
select 新id=right(10001+min(id),4)
from tb a
where not exists(
        select * from tb where id=right(10001+a.id,4))
go

--删除测试
drop table tb

/*--测试结果

新id      
-------- 
0001

(所影响的行数为 1 行)

新id      
-------- 
0003

(所影响的行数为 1 行)
--*/

5.16 学号

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


--创建测试表
create table tb(id varchar(12)
        ,YXDM varchar(3),ZYDM varchar(4))
go

--创建触发器,生成ID号
create trigger t_insert on tb
instead of insert
as
select * into #t from inserted order by yxdm,zydm
declare @yxdm varchar(3),@zydm varchar(4),@id int

update #t set @id=case when @yxdm=yxdm and @zydm=zydm then @id+1 else dbo.f_getid(yxdm,zydm) end
    ,id='GY'+right(yxdm,2)+@zydm+right('0000'+cast(@id as varchar),4)
    ,@yxdm=yxdm,@zydm=zydm

insert into tb
    select * from #t
go

--插入数据测试
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0002')
insert into tb(YXDM,ZYDM) values('001','0002')
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0003')
insert into tb(YXDM,ZYDM) values('001','0003')
insert into tb(YXDM,ZYDM) values('002','0001')
insert into tb(YXDM,ZYDM) values('002','0001')
insert into tb(YXDM,ZYDM) values('002','0001')

--显示结果
select * from tb order by id

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

5.17 以另一表的字段生成编号

--创建自定义函数,得到新的ID
create function f_getid(
@BookIndex_ISBN char(25))
returns int
as
begin
    return(
        select isnull(right(max(BookIndex_Bar_Code),7),0)+10000001
        from 图书索引表 
        where BookIndex_ISBN=@BookIndex_ISBN
    )
end
go

--创建测试表
create table 图书基本信息表(
Book_ISBN char(25) not null primary key,
Book_Category char(3) null)
insert 图书基本信息表 select 'A001','001'
union  all           select 'A002','002'

create table 图书索引表(
BookIndex_Index char(20) null,
BookIndex_Bar_Code char(10) null,
BookIndex_ISBN char(25) not null
    constraint fk_BookIndex_ISBN_Book_ISBN foreign key
        references 图书基本信息表(Book_ISBN) 
        on update cascade on delete cascade)
go

--创建触发器,自动生成图书索引号
create trigger t_insert on 图书索引表
instead of insert
as
select * into #t from inserted order by BookIndex_ISBN

declare @BookIndex_ISBN char(25),@id int
update a 
    set @id=case 
            when @BookIndex_ISBN=a.BookIndex_ISBN
            then @id+1 else dbo.f_getid(BookIndex_ISBN) 
        end
    ,BookIndex_Bar_Code=b.Book_Category+right(@id,7)
    ,@BookIndex_ISBN=a.BookIndex_ISBN
from #t a join 图书基本信息表 b on a.BookIndex_ISBN=b.Book_ISBN

insert into 图书索引表 select * from #t
go

--插入数据测试
insert into 图书索引表(BookIndex_Index,BookIndex_ISBN)
select 'A01','A001'
union all select 'A02','A002'
union all select 'A03','A001'

insert into 图书索引表(BookIndex_Index,BookIndex_ISBN)
select 'B01','A001'
union all select 'B02','A002'

--显示结果
select * from 图书索引表
go

--删除测试
drop table 图书索引表,图书基本信息表
drop function f_getid

/*--测试结果

BookIndex_Index      BookIndex_Bar_Code BookIndex_ISBN            
-------------------- ------------------ ------------------------- 
A01                  0010000001         A001                     
A03                  0010000002         A001                     
A02                  0020000001         A002                     
B01                  0010000003         A001                     
B02                  0020000002         A002                     

(所影响的行数为 5 行)
--*/

5.18 以另一个表的字段做默认值

--以另一个表的字段做默认值

--创建测试表
create table tb1(id int,name varchar(10))
insert into tb1
select 1,'张三'
union all select 2,'李四'

--测试表2,其中name是根据id字段的值从tb1中取得的.
create table tb2(id int,name varchar(10),sex int)
go

--创建处理的触发器
create trigger t_insert on tb2
after insert
as
update tb2 set name=b.name
from tb2 a
    join tb1 b on a.id=b.id
    join inserted  c on a.id=c.id
go

--插入数据测试
insert into tb2(id,sex)
select 1,10
union all select 1,20
union all select 2,20

--显示结果
select * from tb2

go
--删除测试环境
drop table tb1,tb2


 

5.19 自已做标识列的例子

 

--自已做标识列的例子:

--创建得到最大id的函数
create function f_getid()
returns int
as
begin
    return(select isnull(max(id),0)+1 from tb)
end
go

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

--创建触发器,在删除表中的记录时,自动更新记录的id(**如果不要此功能,则删除此触发器)
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
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

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

/*--测试结果
id          name       
----------- ---------- 
1           张三
2           张四
3           张五
4           张六
5           张七
6           张八
7           张九
8           张十

(所影响的行数为 8 行)

id          name       
----------- ---------- 
1           张三
2           张四
3           张六
4           张九

(所影响的行数为 4 行)
--*/

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值