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