SQL语句 对表按某几个字段分组,并生成组内记录数范围内的随机序号.
来源：互联网 发布：淘宝网商贷提前还款 编辑：程序博客网 时间：2024/04/19 12:43
CREATE TABLE gw_card
(
id INT IDENTITY(1,1),
address VARCHAR(100),
subjecth INT,
subject VARCHAR(10),
id_key VARCHAR(10)
)
INSERT gw_card SELECT 'aa',1,'xx',NULL
UNION ALL SELECT 'aa',1,'xx',NULL
UNION ALL SELECT 'bb',1,'xx',NULL
UNION ALL SELECT 'cc',1,'yy',NULL
UNION ALL SELECT 'bb',1,'xx',NULL
UNION ALL SELECT 'aa',1,'xx',NULL
UNION ALL SELECT 'aa',1,'xx',NULL
GO
--查看原始数据
SELECT * FROM gw_card
/**//*
1 aa 1 xx NULL
2 aa 1 xx NULL
3 bb 1 xx NULL
4 cc 1 yy NULL
5 bb 1 xx NULL
6 aa 1 xx NULL
7 aa 1 xx NULL
*/
GO
--得到要生成的取机数的总数
DECLARE @n INT
SELECT @n=COUNT(*) FROM gw_card
SET ROWCOUNT @n
--创建生成随机数据的临时表
CREATE TABLE #(id INT IDENTITY,gid INT,x INT)
--按科目和地址分组,得到每组的记录数,及待分配的随机数值
INSERT #
SELECT DISTINCT gid,CAST(RAND(CHECKSUM(NEWID())) * cnt + 1 AS INT)
FROM sysobjects a
CROSS JOIN
(SELECT CHECKSUM(address,subjecth,subject) gid,COUNT(*) cnt
FROM gw_card
GROUP BY address,subjecth,subject
) b
SET ROWCOUNT 0
GO
--进行更新操作
UPDATE e SET e.id_key = RIGHT('0000000000' + RTRIM(x.x),10)
--SELECT e.*,RIGHT('0000000000' + RTRIM(x.x),10),CHECKSUM(address,subjecth,subject),gid,x.id,x
FROM gw_card e
INNER JOIN # x
ON CHECKSUM(address,subjecth,subject)=gid
AND (SELECT COUNT(*) FROM #
WHERE gid = x.gid
AND id <= x.id
)=
(
SELECT COUNT(*) FROM gw_card
WHERE /**//*address = e.address
AND subjecth = e.subjecth
AND subject = e.subject*/
CHECKSUM(address,subjecth,subject)=CHECKSUM(e.address,e.subjecth,e.subject)
AND id<=e.id)
/**//*这个地方,楼主还连的有其它表, 因为它们不影响分配id_key的值,所以我也就没有建相应的测试表及测试数据,楼主可以自行加上*/
GO
--按地址及科目分组顺序 查看更改后的id_key值.可以看到同一组的id_card已获得更新,并且值在每组个数的范围内随机分配,且不重复
SELECT * FROM gw_card
ORDER BY address,subjecth,subject,id
/**//*
1 aa 1 xx 0000000004
2 aa 1 xx 0000000001
6 aa 1 xx 0000000003
7 aa 1 xx 0000000002
3 bb 1 xx 0000000002
5 bb 1 xx 0000000001
4 cc 1 yy 0000000001
*/
GO
DROP TABLE #,gw_card
GO
