34,588
社区成员
发帖
与我相关
我的任务
分享
create table t
(
id int,
name varchar(10)
)
insert into t
select 1,'a' union
select 2,'b' union
select 3,'c' union
select 4,'c' union
select 5,'b' union
select 6,'a' union
select 7,'b' union
select 8,'c' union
select 9,'c' union
select 10,'b'
select t.name,(SELECT TOP 1 id FROM t a WHERE a.NAME=t.NAME ORDER BY CHECKSUM(NEWID()))id
from t
group by t.name
drop table t
[/quote]
我尝试了print CHECKSUM(NEWID()),你可以尝试一下,数据值非常大或者非常小。[/quote]
这样的值来排序不挺好吗?
print CHECKSUM(NEWID())
GO 100
/*
开始执行循环
1581471204
1348252016
1996724381
-1980841283
-1564630166
-1902451299
-10457622
1831699115
-708607447
2033937001
-1915539893
-484983626
-1868583336
356238476
589645462
937635699
-1346684713
1331966655
1512007825
1865893817
-1063556626
543179299
-328760050
-271327220
1189543194
-1510508131
-118510832
-355247300
-1973015479
644905109
-1852408505
18630223
-845301686
-1080522957
1870927274
291261448
-180236195
1816085609
-56195279
-1955612826
-1823239235
164804350
464041650
496189191
-992863046
1798310361
-1122539631
-1285659985
-666769039
-55363285
-987353338
-1434990918
-1827311849
-305431349
-808734243
185198454
418573207
1516953218
1882558559
-1850485502
1695476463
968570419
-478270604
1135814936
4560807
-977845055
-1500413753
1247482029
-2029953002
-143514433
1413689305
-89941
-1830812283
-1358386518
2027708682
-2091711200
-860666170
-298014650
-1645890234
1924256339
-1106033668
1664048751
-1010700298
1027476459
1534224863
-340552660
1324855273
-1527893151
-2130199246
-1740334248
950218794
2129991177
-676034078
853947593
-1098546149
-1000674271
-2145996591
1857163210
-323222553
638988058
批处理执行已完成 100 次。
*/
[/quote]
嗯 确实,没有问题。
--T-SQL:
select abs(checksum(newid()))%10 //生成随机数取值范围 0-9
select cast( floor(rand()*10) as int) //生成随机数取值范围 0-10
select a+abs(checksum(newid()))%(b-a+1) //生成随机数取值范围 a-b
URL:http://www.cnblogs.com/worfdream/articles/3045160.htmlcreate table t
(
id int,
name varchar(10)
)
insert into t
select 1,'a' union
select 2,'b' union
select 3,'c' union
select 4,'c' union
select 5,'b' union
select 6,'a' union
select 7,'b' union
select 8,'c' union
select 9,'c' union
select 10,'b'
select t.name,(SELECT TOP 1 id FROM t a WHERE a.NAME=t.NAME ORDER BY CHECKSUM(NEWID()))id
from t
group by t.name
drop table t
[/quote]
我尝试了print CHECKSUM(NEWID()),你可以尝试一下,数据值非常大或者非常小。[/quote]
这样的值来排序不挺好吗?
print CHECKSUM(NEWID())
GO 100
/*
开始执行循环
1581471204
1348252016
1996724381
-1980841283
-1564630166
-1902451299
-10457622
1831699115
-708607447
2033937001
-1915539893
-484983626
-1868583336
356238476
589645462
937635699
-1346684713
1331966655
1512007825
1865893817
-1063556626
543179299
-328760050
-271327220
1189543194
-1510508131
-118510832
-355247300
-1973015479
644905109
-1852408505
18630223
-845301686
-1080522957
1870927274
291261448
-180236195
1816085609
-56195279
-1955612826
-1823239235
164804350
464041650
496189191
-992863046
1798310361
-1122539631
-1285659985
-666769039
-55363285
-987353338
-1434990918
-1827311849
-305431349
-808734243
185198454
418573207
1516953218
1882558559
-1850485502
1695476463
968570419
-478270604
1135814936
4560807
-977845055
-1500413753
1247482029
-2029953002
-143514433
1413689305
-89941
-1830812283
-1358386518
2027708682
-2091711200
-860666170
-298014650
-1645890234
1924256339
-1106033668
1664048751
-1010700298
1027476459
1534224863
-340552660
1324855273
-1527893151
-2130199246
-1740334248
950218794
2129991177
-676034078
853947593
-1098546149
-1000674271
-2145996591
1857163210
-323222553
638988058
批处理执行已完成 100 次。
*/
create table t
(
id int,
name varchar(10)
)
insert into t
select 1,'a' union
select 2,'b' union
select 3,'c' union
select 4,'c' union
select 5,'b' union
select 6,'a' union
select 7,'b' union
select 8,'c' union
select 9,'c' union
select 10,'b'
select t.name,(SELECT TOP 1 id FROM t a WHERE a.NAME=t.NAME ORDER BY CHECKSUM(NEWID()))id
from t
group by t.name
drop table t
[/quote]
我尝试了print CHECKSUM(NEWID()),你可以尝试一下,数据值非常大或者非常小。create table t
(
id int,
name varchar(10)
)
insert into t
select 1,'a' union
select 2,'b' union
select 3,'c' union
select 4,'c' union
select 5,'b' union
select 6,'a' union
select 7,'b' union
select 8,'c' union
select 9,'c' union
select 10,'b'
select t.name,(SELECT TOP 1 id FROM t a WHERE a.NAME=t.NAME ORDER BY CHECKSUM(NEWID()))id
from t
group by t.name
drop table t
create table t
(
id int,
name varchar(10)
)
insert into t
select 1,'a' union
select 2,'b' union
select 3,'c' union
select 4,'c' union
select 5,'b'
select t.name,(SELECT TOP 1 id FROM t a WHERE a.NAME=t.NAME ORDER BY RAND()*100)id
from t
group by t.name
--RAND()*100 100为一个基数根据本身id列值得范围进行修改
drop table t
declare @tab table(id int,name char(1))
insert into @tab
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'c' union all
select 5,'b'
;with sel as(
select *,ROW_NUMBER() over(partition by name order by newid()) as row from @tab)
select ID,name from sel where row=1
create table t
(
id int,
name varchar(10)
)
insert into t
select 1,'a' union
select 2,'b' union
select 3,'c' union
select 4,'c' union
select 5,'b'
select t.name,(SELECT TOP 1 id FROM t a WHERE a.NAME=t.NAME ORDER BY CHECKSUM(NEWID()))id
from t
group by t.name
drop table t
create table t
(
id int,
name varchar(10)
)
insert into t
select 1,'a' union
select 2,'b' union
select 3,'c' union
select 4,'c' union
select 5,'b'
select t.name,MAX(id)
from t
group by t.name
drop table t