select top(20) PaperId,CreationTime from TB_Paper where QuestionTypeId=1 and PaperDegree ='1' union all
select top(30) PaperId,CreationTime from TB_Paper where QuestionTypeId=1 and PaperDegree ='2' ORDER BY NEWID()我想要随机查询出满足的条件 根据NEWID() 可是 这样写会报出消息 104,级别 16,状态 1,第 1 行
如果该语句包含 UNION、INTERSECT 或 EXCEPT 运算符,则 ORDER BY 项必须出现在选择列表中。和排序字段的时候是一样的 必须要将排序字段同时查询进去,可是这是个随机函数,没办法同时查询 有什么办法解决吗?数据库是sql server的
select top(30) PaperId,CreationTime from TB_Paper where QuestionTypeId=1 and PaperDegree ='2' ORDER BY NEWID()我想要随机查询出满足的条件 根据NEWID() 可是 这样写会报出消息 104,级别 16,状态 1,第 1 行
如果该语句包含 UNION、INTERSECT 或 EXCEPT 运算符,则 ORDER BY 项必须出现在选择列表中。和排序字段的时候是一样的 必须要将排序字段同时查询进去,可是这是个随机函数,没办法同时查询 有什么办法解决吗?数据库是sql server的
--第一个select里加个order by 吧!
select top(20) PaperId,CreationTime
from TB_Paper
where QuestionTypeId=1 and PaperDegree ='1' ORDER BY PaperId
union all
select top(30) PaperId,CreationTime
from TB_Paper
where QuestionTypeId=1 and PaperDegree ='2' ORDER BY NEWID()/*另外建议楼主写SQL时关键字大小写一致!*/
SELECT *
FROM (
SELECT TOP(20)
PaperId,CreationTime
FROM TB_Paper
WHERE QuestionTypeId=1 AND PaperDegree ='1'
UNION ALL
SELECT TOP(30)
PaperId,CreationTime
FROM TB_Paper
WHERE QuestionTypeId=1 AND PaperDegree ='2'
) tmp
ORDER BY NEWID()
SELECT *
FROM (
SELECT TOP(20) PaperId,CreationTime
FROM TB_Paper WHERE QuestionTypeId=1 AND PaperDegree ='1'
UNION ALL
SELECT TOP(30) PaperId,CreationTime
FROM TB_Paper WHERE QuestionTypeId=1 AND PaperDegree ='2'
) a
ORDER BY NEWID()
union all
select top 100 percent * from (select top30 PaperId,CreationTime from TB_Paper where QuestionTypeId=1 and PaperDegree ='2' ORDER BY NEWID())
union all
select top 100 percent * from (select top30 PaperId,CreationTime from TB_Paper where QuestionTypeId=1 and PaperDegree ='2' ORDER BY NEWID()) t