Record需有主键字段, 设Record表主键字段是id, 修改的SQL如下,update Record set Merchant='111' where id in (select top 100 id from #t where [time]>=cast(convert(varchar,getdate(),23)+' 00:00:00' as datetime) and [time]<cast(convert(varchar,dateadd(d,1,getdate()),23)+' 00:00:00' as datetime) order by newid())
USE tempdb GO IF OBJECT_ID('dbo.[t]') IS NOT NULL DROP TABLE dbo.[t] GO CREATE TABLE dbo.[t]( [学生id] int ,[班级id] VARCHAR(10) ) GO SET NOCOUNT ON INSERT INTO dbo.[t] VALUES(N'1',N'101') INSERT INTO dbo.[t] VALUES(N'2',N'101') INSERT INTO dbo.[t] VALUES(N'3',N'101') INSERT INTO dbo.[t] VALUES(N'4',N'101') INSERT INTO dbo.[t] VALUES(N'5',N'102') INSERT INTO dbo.[t] VALUES(N'6',N'102') INSERT INTO dbo.[t] VALUES(N'7',N'201')SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY [班级id] ORDER BY NEWID()) AS rid,* FROM t ) AS tt WHERE tt.rid=1
UPDATE T SET Merchant='111' FROM (SELECT *,ROW_NUMBER()OVER(ORDER BY NEWID()) AS RN FROM Record WHERE Merchant='000' WHERE FORMAT([time],'yyyyMMdd')=FORMAT(GETDATE(),'yyyyMMdd')) AS T WHERE RN<=100
set Merchant='111'
where id in (select top 100 id
from #t
where [time]>=cast(convert(varchar,getdate(),23)+' 00:00:00' as datetime)
and [time]<cast(convert(varchar,dateadd(d,1,getdate()),23)+' 00:00:00' as datetime)
order by newid())
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[学生id] int
,[班级id] VARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'101')
INSERT INTO dbo.[t] VALUES(N'2',N'101')
INSERT INTO dbo.[t] VALUES(N'3',N'101')
INSERT INTO dbo.[t] VALUES(N'4',N'101')
INSERT INTO dbo.[t] VALUES(N'5',N'102')
INSERT INTO dbo.[t] VALUES(N'6',N'102')
INSERT INTO dbo.[t] VALUES(N'7',N'201')SELECT * FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY [班级id] ORDER BY NEWID()) AS rid,* FROM t
) AS tt
WHERE tt.rid=1