Question

sql如何可以每行都显示不同的随机数？

任意两表abc，和def
select rand() as id from abc

select d.id from def,( select top 1 id, row_number() over (order by newid()) as xu from abc) as d
上面两查询到的两个结果为何每行都相同，如何随机行

Answer 1

select cast(ceiling(rand(checksum(newid()))* 10000) as int) as id from abc

Answer 2

sql中每一行显示随机数根据数据库的不同有不同的写法：
1、在MySQL中写法如下:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
2、在PostgreSQL中写法如下:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
3、在Microsoft SQL Server写法如下:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
在IBM DB2中写法如下？：
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Thanks Tim
在Oracle中写法如下:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1