有点疑惑

Question

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条记录是随机取出来的

Answer 1

试试这个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())

Answer 2

提示 “'unionselect' 附近有语法错误”
我的是sql 2000

Answer 3

'unionselect' 这个错误不关我那个事，是csdn编辑界面的错。你把语句调整过来试试，不过我没2000，不知道有没有checksum函数

Answer 4

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())
查询结果和原来一样

Answer 5

下面是我在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 次。
 
 */

Answer 6

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

Answer 7

单独执行这句是可以的
上面已经说了
但是使用union之后就不行了