有点疑惑
select id,newid() from dizigui where id = 342
union
select top 3 id,newid() from dizigui where id != 341 and id != 342 order by newid()
执行这条语句时
除342这条记录
剩余的3条总是表的前3条记录
只是顺序不同而已
而
select top 3 id,newid() from dizigui where id != 341 and id != 342 order by newid()
单独执行这条语句
总是能随机的抽取3条记录
---为什么呢
我的目的是想除342这叫记录之外，另外3条记录是随机取出来的
[最优解释]
试试这个
select id,newid() from dizigui where id = 342
union
select top 3 id,newid() from dizigui where id != 341 and id != 342 order by checksum(newid())
[其他解释]
select Pid from Product where pid=20
union
select PID from (select top 3 PID,NEWID() as a from Product order by checksum(newid()))A
[其他解释]
按楼主的方式，我运行出结果正常啊。
[其他解释]
提示 “'unionselect' 附近有语法错误”
我的是sql 2000
[其他解释]
'unionselect' 这个错误不关我那个事，是csdn编辑界面的错。你把语句调整过来试试，不过我没2000，不知道有没有checksum函数
[其他解释]
sql 2000 中有这个函数
但是提示 “如果语句中包含 UNION 运算符，那么 ORDER BY 子句中的项就必须出现在选择列表中。”
换成这样
select id,checksum(newid()) from dizigui where id = 342
union
select top 3 id,checksum(newid()) from dizigui where id != 341 and id != 342 order by checksum(newid())
查询结果和原来一样
[其他解释]
下面是我在2008的AdventureWorks库上做的测试，并且我也找了写资料，CHECKSUM(NEWID())可以实现随机抽取数据。语法是2008才有，2000会报错的，但是证明了是可以的。你多试几次看看，不过如果你的表里面就只有那么几条数据。那就没用了
SELECT TOP 10 * FROM HumanResources.Department WHERE DepartmentID NOT IN (8,10) ORDER BY CHECKSUM(NEWID())
GO 5
/*
开始执行循环
DepartmentID Name GroupName ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
4 Marketing Sales and Marketing 1998-06-01 00:00:00.000
14 Facilities and Maintenance Executive General and Administration 1998-06-01 00:00:00.000
7 Production Manufacturing 1998-06-01 00:00:00.000
1 Engineering Research and Development 1998-06-01 00:00:00.000
11 Information Services Executive General and Administration 1998-06-01 00:00:00.000
2 Tool Design Research and Development 1998-06-01 00:00:00.000
12 Document Control Quality Assurance 1998-06-01 00:00:00.000
5 Purchasing Inventory Management 1998-06-01 00:00:00.000
13 Quality Assurance Quality Assurance 1998-06-01 00:00:00.000
6 Research and Development Research and Development 1998-06-01 00:00:00.000
(10 行受影响)
DepartmentID Name GroupName ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
14 Facilities and Maintenance Executive General and Administration 1998-06-01 00:00:00.000
11 Information Services Executive General and Administration 1998-06-01 00:00:00.000
6 Research and Development Research and Development 1998-06-01 00:00:00.000
1 Engineering Research and Development 1998-06-01 00:00:00.000
9 Human Resources Executive General and Administration 1998-06-01 00:00:00.000
12 Document Control Quality Assurance 1998-06-01 00:00:00.000
7 Production Manufacturing 1998-06-01 00:00:00.000
5 Purchasing Inventory Management 1998-06-01 00:00:00.000
15 Shipping and Receiving Inventory Management 1998-06-01 00:00:00.000
2 Tool Design Research and Development 1998-06-01 00:00:00.000
(10 行受影响)
DepartmentID Name GroupName ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
15 Shipping and Receiving Inventory Management 1998-06-01 00:00:00.000
9 Human Resources Executive General and Administration 1998-06-01 00:00:00.000
13 Quality Assurance Quality Assurance 1998-06-01 00:00:00.000
5 Purchasing Inventory Management 1998-06-01 00:00:00.000
16 Executive Executive General and Administration 1998-06-01 00:00:00.000
11 Information Services Executive General and Administration 1998-06-01 00:00:00.000
2 Tool Design Research and Development 1998-06-01 00:00:00.000
12 Document Control Quality Assurance 1998-06-01 00:00:00.000
1 Engineering Research and Development 1998-06-01 00:00:00.000
6 Research and Development Research and Development 1998-06-01 00:00:00.000
(10 行受影响)
DepartmentID Name GroupName ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
5 Purchasing Inventory Management 1998-06-01 00:00:00.000
11 Information Services Executive General and Administration 1998-06-01 00:00:00.000
4 Marketing Sales and Marketing 1998-06-01 00:00:00.000
14 Facilities and Maintenance Executive General and Administration 1998-06-01 00:00:00.000
9 Human Resources Executive General and Administration 1998-06-01 00:00:00.000
13 Quality Assurance Quality Assurance 1998-06-01 00:00:00.000
15 Shipping and Receiving Inventory Management 1998-06-01 00:00:00.000
12 Document Control Quality Assurance 1998-06-01 00:00:00.000
7 Production Manufacturing 1998-06-01 00:00:00.000
3 Sales Sales and Marketing 1998-06-01 00:00:00.000
(10 行受影响)
DepartmentID Name GroupName ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
14 Facilities and Maintenance Executive General and Administration 1998-06-01 00:00:00.000
5 Purchasing Inventory Management 1998-06-01 00:00:00.000
9 Human Resources Executive General and Administration 1998-06-01 00:00:00.000
1 Engineering Research and Development 1998-06-01 00:00:00.000
13 Quality Assurance Quality Assurance 1998-06-01 00:00:00.000
6 Research and Development Research and Development 1998-06-01 00:00:00.000
16 Executive Executive General and Administration 1998-06-01 00:00:00.000
12 Document Control Quality Assurance 1998-06-01 00:00:00.000
2 Tool Design Research and Development 1998-06-01 00:00:00.000
15 Shipping and Receiving Inventory Management 1998-06-01 00:00:00.000
(10 行受影响)
批处理执行已完成 5 次。
*/
[其他解释]
看看上面是不是你要的
[其他解释]
单独执行这句是可以的
上面已经说了
但是使用union之后就不行了
[其他解释]
这个稍微修改一下可以
但是不明白原来的那样写为什么不行
[其他解释]
那应该是union影响了排序