今天百度知道那里,看到个问题
说 需要一个因日期变更的归零自增字段
在今天0409他会以0409001开始无限增加,
到0410这天会以0410001开始无限增加,以此类推。。
关键是解决001这三位数怎样随日期变更而重置??
下面是测试表,只描述几个关键的字段:
也就是
p_id :自增主键
p_year : 记录的日期
p_num : 准备作为那个流水号的字段.
create table Pdms_polling(
p_id int primary key IDENTITY(1,1) ,
p_year datetime ,
p_num varchar(30)
);
go
-- 这个是我一开始写的触发器
-- 算法是使用 ROW_NUMBER() 对 日期进行分组,按自增主键进行排序
-- 最后是形成一个按日期递增 序号的列表
-- 然后使用 right( cast(power(10,3) as varchar) + 序号, 3) 来将其格式化为 000 的格式
CREATE TRIGGER TriAutoNum
ON Pdms_polling
FOR INSERT
AS
DECLARE
@newID INT,
@newNum varchar(30),
@oldNum varchar(30);
BEGIN
-- 定义游标.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT
p_id,
p_num,
Convert(varchar(10), p_year, 112) +
right(
cast(power(10,3) as varchar)
+ ROW_NUMBER() OVER(PARTITION BY Convert(varchar(10), p_year, 112) ORDER BY p_id)
, 3)
FROM
Pdms_polling
WHERE
Convert(varchar(10), p_year, 112)
IN ( SELECT DISTINCT Convert(varchar(10), p_year, 112) FROM inserted );
-- 打开游标.
OPEN c_test_main;
-- 填充数据.
FETCH NEXT FROM c_test_main INTO @newID, @oldNum, @newNum;
-- 假如检索到了数据,才处理.
WHILE @@fetch_status = 0
BEGIN
IF @oldNum IS NULL BEGIN
UPDATE
Pdms_polling
SET
p_num = @newNum
WHERE
p_id = @newID;
END;
-- 填充下一条数据.
FETCH NEXT FROM c_test_main INTO @newID, @oldNum, @newNum;
END;
-- 关闭游标
CLOSE c_test_main;
-- 释放游标.
DEALLOCATE c_test_main;
END;
go
-- 下面是测试插入一条记录
1> INSERT INTO Pdms_polling (p_year) VALUES( GETDATE() );
2> go
(1 行受影响)
1>
2> SELECT * FROM Pdms_polling;
3> go
p_id p_year p_num
----------- ----------------------- ------------------------------
1 2011-04-09 22:53:32.640 20110409001
(1 行受影响)
1> -- 下面是测试插入多条记录
2> INSERT INTO Pdms_polling (p_year)
3> SELECT GETDATE()
4> UNION ALL SELECT GETDATE()
5> UNION ALL SELECT GETDATE();
6> go
(1 行受影响)
1>
2> SELECT * FROM Pdms_polling;
3> go
p_id p_year p_num
----------- ----------------------- ------------------------------
1 2011-04-09 22:53:32.640 20110409001
2 2011-04-09 22:53:32.840 20110409002
3 2011-04-09 22:53:32.840 20110409003
4 2011-04-09 22:53:32.840 20110409004
(4 行受影响)
1>
2> INSERT INTO Pdms_polling (p_year)
3> SELECT '2011-04-08 10:00:00'
4> UNION ALL SELECT '2011-04-09 10:00:00'
5> UNION ALL SELECT '2011-04-10 10:00:00';
6> go
(1 行受影响)
1> -- 下面是测试插入多条记录 不同天的。
2> SELECT * FROM Pdms_polling;
3> go
p_id p_year p_num
----------- ----------------------- ------------------------------
1 2011-04-09 22:53:32.640 20110409001
2 2011-04-09 22:53:32.840 20110409002
3 2011-04-09 22:53:32.840 20110409003
4 2011-04-09 22:53:32.840 20110409004
5 2011-04-08 10:00:00.000 20110408001
6 2011-04-09 10:00:00.000 20110409005
7 2011-04-10 10:00:00.000 20110410001
(7 行受影响)
写好以后,又回去看看那个问题。
发现别人用 CTE 来处理,效果也不错。
回头我再来修改修改我的触发器,也使用 CTE 来处理。
这样就可以不用游标了。代码也简短了不少。
ALTER TRIGGER TriAutoNum
ON Pdms_polling
FOR INSERT
AS
BEGIN
WITH
cte
AS
(
SELECT
p_id,
p_num,
Convert(varchar(10), p_year, 112) +
right(
cast(power(10,3) as varchar)
+ ROW_NUMBER() OVER(PARTITION BY Convert(varchar(10), p_year, 112) ORDER BY p_id)
, 3) AS new_p_num
FROM
Pdms_polling
WHERE
Convert(varchar(10), p_year, 112)
IN ( SELECT DISTINCT Convert(varchar(10), p_year, 112) FROM inserted )
)
UPDATE
Pdms_polling
SET
Pdms_polling.p_num = cte.new_p_num
FROM
Pdms_polling
JOIN cte
ON (Pdms_polling.p_id = cte.p_id)
WHERE
Pdms_polling.p_num IS NULL;
END;
go
-- 下面是测试插入一条记录
1> INSERT INTO Pdms_polling (p_year) VALUES( GETDATE() );
2> go
(1 行受影响)
1>
2> SELECT * FROM Pdms_polling;
3> go
p_id p_year p_num
----------- ----------------------- ------------------------------
1 2011-04-09 22:53:32.640 20110409001
2 2011-04-09 22:53:32.840 20110409002
3 2011-04-09 22:53:32.840 20110409003
4 2011-04-09 22:53:32.840 20110409004
5 2011-04-08 10:00:00.000 20110408001
6 2011-04-09 10:00:00.000 20110409005
7 2011-04-10 10:00:00.000 20110410001
8 2011-04-09 23:10:27.687 20110409006
(8 行受影响)
1> -- 下面是测试插入多条记录
2> INSERT INTO Pdms_polling (p_year)
3> SELECT GETDATE()
4> UNION ALL SELECT GETDATE()
5> UNION ALL SELECT GETDATE();
6> go
(3 行受影响)
1>
2> SELECT * FROM Pdms_polling;
3> go
p_id p_year p_num
----------- ----------------------- ------------------------------
1 2011-04-09 22:53:32.640 20110409001
2 2011-04-09 22:53:32.840 20110409002
3 2011-04-09 22:53:32.840 20110409003
4 2011-04-09 22:53:32.840 20110409004
5 2011-04-08 10:00:00.000 20110408001
6 2011-04-09 10:00:00.000 20110409005
7 2011-04-10 10:00:00.000 20110410001
8 2011-04-09 23:10:27.687 20110409006
9 2011-04-09 23:10:27.733 20110409007
10 2011-04-09 23:10:27.733 20110409008
11 2011-04-09 23:10:27.733 20110409009
(11 行受影响)
1> -- 下面是测试插入多条记录 不同天的。
2> INSERT INTO Pdms_polling (p_year)
3> SELECT '2011-04-08 10:00:00'
4> UNION ALL SELECT '2011-04-09 10:00:00'
5> UNION ALL SELECT '2011-04-10 10:00:00';
6> go
(3 行受影响)
1>
2> SELECT * FROM Pdms_polling;
3> go
p_id p_year p_num
----------- ----------------------- ------------------------------
1 2011-04-09 22:53:32.640 20110409001
2 2011-04-09 22:53:32.840 20110409002
3 2011-04-09 22:53:32.840 20110409003
4 2011-04-09 22:53:32.840 20110409004
5 2011-04-08 10:00:00.000 20110408001
6 2011-04-09 10:00:00.000 20110409005
7 2011-04-10 10:00:00.000 20110410001
8 2011-04-09 23:10:27.687 20110409006
9 2011-04-09 23:10:27.733 20110409007
10 2011-04-09 23:10:27.733 20110409008
11 2011-04-09 23:10:27.733 20110409009
12 2011-04-08 10:00:00.000 20110408002
13 2011-04-09 10:00:00.000 20110409010
14 2011-04-10 10:00:00.000 20110410002
(14 行受影响)