有表xx 两个字段 编号 char(10),日期 datetime
编号 日期
200901010001
200901010002
200901010003
200901010004
200901010005
200901010006
要求每行随机生成的日期应该在前一个编号和后一个编号的时间之间.年月日为编号的前8位。如何能快速实现
编号 日期
200901010001
200901010002
200901010003
200901010004
200901010005
200901010006
要求每行随机生成的日期应该在前一个编号和后一个编号的时间之间.年月日为编号的前8位。如何能快速实现
master..spt_values
INSERT @a SELECT '200901010001',null
union all select '200901010002',NULL
union all select '200901010003',null
union all select '200901020004',NULL
union all select '200901040005',null
union all select '200901050006',NULL
DECLARE @b TABLE(a VARCHAR(20),ma SMALLDATETIME,mi SMALLDATETIME)
INSERT @b
SELECT a,b=ISNULL((SELECT max(left(a,8)) FROM @a where left(a,8)<a.a1 ),a1)
,
ISNULL((SELECT min(left(a,8)) FROM @a where left(a,8)>a.a1 ),a1)FROM (select a,left(a,8) a1,b from @a) a SELECT a, DATEADD(s, FLOOR((3600*24-1)*RAND(CHECKSUM(NEWID()))), DATEADD(DAY, floor(DATEDIFF(DAY,ma,mi)*RAND(ABS(CHECKSUM(NEWID())))),ma))
FROM @b
--result
/*a
-------------------- --------------------------------------------------
200901010001 2009-01-01 01:20:00
200901010002 2009-01-01 08:31:00
200901010003 2009-01-01 09:41:00
200901020004 2009-01-01 16:03:00
200901040005 2009-01-04 09:28:00
200901050006 2009-01-04 15:17:00(所影响的行数为 6 行)*/
@minNumber int, --随机数最小值
@rows int --要取得的行数
select @maxNumber=10000, @minNumber=10000,
@rows=10
set rowcount @rows
select distinct '1234 4567 '+convert(varchar,ID)+' '+convert(varchar,ID)+' '+convert(varchar,ID)+' '+convert(varchar,ID) as 'B'
from (
select convert(int,rand(checksum(newid()))*@minNumber) as ID
from syscolumns,sysobjects
)t
set rowcount 0
-----------------------------------------------------------------------
declare @num1 int ,@num2 int ,@num3 int ,@num4 int
set @num1=rand(abs(convert(int,checksum(newid()))))*10000
set @num2=rand(abs(convert(int,checksum(newid()))))*10000
set @num3=rand(abs(convert(int,checksum(newid()))))*10000
set @num4=rand(abs(convert(int,checksum(newid()))))*10000
select convert(varchar(100),@num1)+' '+convert(varchar(100),@num2)+' '+convert(varchar(100),@num3)+' '+convert(varchar(100),@num4)
---------------------------------------------------------------------------
declare @r1 numeric (15,0),@r2 numeric (15,0)
SELECT @r1=RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )*10000
print @r1
IF OBJECT_ID('[ysa]') IS NOT NULL
DROP TABLE [ysa]
GO
CREATE TABLE [ysa] ([日期] [datetime],[票号] [nvarchar](20))
INSERT INTO [ysa]
SELECT '1900-01-01 00:00:00.000','R10C201001010001' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010002' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010003' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010004' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010005' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010006' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010007' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010008' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010009' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010010' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010011' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010012' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010013' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010014'
-->SQL查询如下:
;WITH t AS
(
SELECT *, DATEADD(
mi, FLOOR(
60.*(17-8)/COUNT(1)OVER(PARTITION BY SUBSTRING(票号, 5, 8))
)*(
ROW_NUMBER()OVER(PARTITION BY SUBSTRING(票号, 5, 8) ORDER BY 票号)-1
), SUBSTRING(票号, 5, 8)+' 8:00:'+LTRIM(ABS(CHECKSUM(NEWID()))%60)
) AS newdate
FROM [ysa]
)
UPDATE t
SET 日期=newdateSELECT * FROM ysa
/*
日期 票号
----------------------- --------------------
2010-01-01 08:00:51.000 R10C201001010001
2010-01-01 08:38:17.000 R10C201001010002
2010-01-01 09:16:04.000 R10C201001010003
2010-01-01 09:54:39.000 R10C201001010004
2010-01-01 10:32:08.000 R10C201001010005
2010-01-01 11:10:41.000 R10C201001010006
2010-01-01 11:48:05.000 R10C201001010007
2010-01-01 12:26:57.000 R10C201001010008
2010-01-01 13:04:51.000 R10C201001010009
2010-01-01 13:42:42.000 R10C201001010010
2010-01-01 14:20:01.000 R10C201001010011
2010-01-01 14:58:22.000 R10C201001010012
2010-01-01 15:36:03.000 R10C201001010013
2010-01-01 16:14:39.000 R10C201001010014(14 行受影响)*/
IF OBJECT_ID('[ysa]') IS NOT NULL
DROP TABLE [ysa]
GO
CREATE TABLE [ysa] ([日期] [datetime],[票号] [nvarchar](20))
INSERT INTO [ysa]
SELECT '1900-01-01 00:00:00.000','R10C201001010001' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010002' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010003' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010004' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010005' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010006' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010007' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010008' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010009' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010010' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010011' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010012' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010013' UNION ALL
SELECT '1900-01-01 00:00:00.000','R10C201001010014'
-->SQL2000查询如下:
UPDATE [ysa]
SET 日期 = newdate
FROM (
SELECT *, DATEADD(
mi, FLOOR(
60.*(17-8)/(
SELECT COUNT(1)
FROM ysa
WHERE SUBSTRING(票号, 5, 8) = SUBSTRING(t.票号, 5, 8)
)
)*(
(
SELECT COUNT(1)
FROM ysa
WHERE SUBSTRING(票号, 5, 8) = SUBSTRING(t.票号, 5, 8)
AND 票号<t.票号
)
), SUBSTRING(票号, 5, 8)+' 8:00:'+LTRIM(ABS(CHECKSUM(NEWID()))%60)
) AS newdate
FROM [ysa] t
) t
WHERE [ysa].票号 = t.票号
SELECT * FROM ysa
/*
日期 票号
----------------------- --------------------
2010-01-01 08:00:32.000 R10C201001010001
2010-01-01 08:38:28.000 R10C201001010002
2010-01-01 09:16:32.000 R10C201001010003
2010-01-01 09:54:36.000 R10C201001010004
2010-01-01 10:32:48.000 R10C201001010005
2010-01-01 11:10:33.000 R10C201001010006
2010-01-01 11:48:47.000 R10C201001010007
2010-01-01 12:26:49.000 R10C201001010008
2010-01-01 13:04:37.000 R10C201001010009
2010-01-01 13:42:01.000 R10C201001010010
2010-01-01 14:20:18.000 R10C201001010011
2010-01-01 14:58:33.000 R10C201001010012
2010-01-01 15:36:40.000 R10C201001010013
2010-01-01 16:14:13.000 R10C201001010014(14 行受影响)
*/SQL2000的试试这段代码