sqlserver:分组随机取记录后，newID和union的冲突解决

 

    刚才在CSDN回答了一个问题，数据库抽取30个单选题，10个多选，10个判断
 

 想都没有想就敲下了语句：

 

select top 30 * from test where typename='单选' order by newid()

union all

select top 10 * from test where typename='多选' order by newid()

union all

select top 10* from test where typename='判断' order by newid()

 

后来楼主说报错：

 

服务器: 消息 156，级别 15，状态 1，行 2
在关键字 'union' 附近有语法错误。
服务器: 消息 156，级别 15，状态 1，行 4
在关键字 'union' 附近有语法错误。

 

后来想了想，应该用 derived table

 

SELECT * FROM 
(select top 30 * from exam_test where testtype=0 order by newid()) as a
UNION ALL
SELECT * FROM 
(select top 10 * from exam_test where testtype=1 order by newid()) as b
UNION ALL
SELECT * FROM 
(select top 10 * from exam_test where testtype=2 order by newid()) as c

 

这次应该可以了吧，而且在网上查了一下，看样子应该是可以了，谁知楼主又说报错：

如果语句中包含 UNION 运算符，那么 ORDER BY 子句中的项就必须出现在选择列表中。

真是郁闷啊，想不通为什么不行？有人告诉我答案吗？望知道的人不吝赐教（：

为了解决问题，只得在每个分组查询加一列，newid()，这相当不舒服。

 

SELECT * FROM  
(select top 30 * ,NewID = NEWID() from exam_test where testtype=0 order by newid()) as a
UNION ALL
SELECT * FROM  
(select top 10 * ,NewID = NEWID() from exam_test where testtype=1 order by newid()) as b
UNION ALL
SELECT * FROM  
(select top 10 * ,NewID = NEWID() from exam_test where testtype=2 order by newid()) as c

 

不知道这样可不可以？应该是不行的，没有测试环境，sorry.  

 

select * from 
(select top 30 * from test where typename='单选' 
union all

select top 10 * from test where typename='多选' 

union all

select top 10* from test where typename='判断' ) as a 
order by newid()