有点疑惑解决办法

有点疑惑

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



[其他解释]
按楼主的方式，我运行出结果正常啊。
[其他解释]

引用:

试试这个


SQL code?



123

select id,newid() from dizigui where id = 342   unionselect top 3 id,newid() from dizigui where id != 341 and id != 342 order by checksum(newid())

提示 “'unionselect' 附近有语法错误”
我的是sql 2000
[其他解释]
'unionselect' 这个错误不关我那个事，是csdn编辑界面的错。你把语句调整过来试试，不过我没2000，不知道有没有checksum函数
[其他解释]

引用:

'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 次。
 
 */

[其他解释]
看看上面是不是你要的


[其他解释]

引用:

下面是我在2008的AdventureWorks库上做的测试，并且我也找了写资料，CHECKSUM(NEWID())可以实现随机抽取数据。语法是2008才有，2000会报错的，但是证明了是可以的。你多试几次看看，不过如果你的表里面就只有那么几条数据。那就没用了
SQL code?1234567891011121314151617181920212223242526272……

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

引用:

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

这个稍微修改一下可以
但是不明白原来的那样写为什么不行

[其他解释]
那应该是union影响了排序

查看更多 下一篇