SQL语句 对表按某几个字段分组,并生成组内记录数范围内的随机序号.

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