产生一个 类似 20110409001 这种格式的流水号
今天百度知道那里，看到个问题说 需要一个因日期变更的归零自增字段在今天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_pollingFOR INSERTAS 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> gop_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> gop_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> gop_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_pollingFOR INSERTAS 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> gop_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> gop_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> gop_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 行受影响)
?
? ? 原文:http://hi.baidu.com/wangzhiqing999/home
