try CREATE PROCEDURE sp_getdxtInfobyDiff @n1 int, @n2 int, @n3 int, @courseId varchar(20) AS BEGINDECLARE @Sql1 varchar(8000),@Sql2 varchar(8000),@Sql3 varchar(8000)SET @Sql1 = 'SELECT top '+ cast(@n1 as varchar(20)) + '* FROM danxuan WHERE courseID='+ char(39) + @courseId + char(39) +' and difficulty= ''易'' order by newid()' --EXEC(@Sql) SET @Sql2 = 'SELECT top '+ cast(@n2 as varchar(20))+ '* FROM danxuan WHERE courseID='+ char(39) + @courseId + char(39) +' and difficulty= ''中'' order by newid()' --EXEC(@Sql) SET @Sql3 = 'SELECT top '+ cast(@n3 as varchar(20)) + '* FROM danxuan WHERE courseID='+ char(39) + @courseId + char(39) +' and difficulty= ''难'' order by newid()'EXEC('select * from (' +@sql1 +') a union all select * from(' +@sql2 +') b union all select * from (' +') c' )END GO
with cte1 as( select top (@n1) * FROM danxuan WHERE courseID= @courseId and difficulty= '易' order by newid() ) ,cte2 as( select top (@n2) * FROM danxuan WHERE courseID= @courseId and difficulty= '中' order by newid() ) ,cte3 as( select top (@n3) * FROM danxuan WHERE courseID= @courseId and difficulty= '难' order by newid() )
select * from cte1 union all select * from cte2 union all select * from cte3 end
josy我用你的方法发现出问题是不是少了个@sql3然后我加上去又报了错误 : 如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。
不好意思,是少了个@sql3 --先把语句打印出来看看 print 'select * from (' +@sql1 +') a union all select * from(' +@sql2 +') b union all select * from (' +@sql3 +') c'--再执行 EXEC('select * from (' +@sql1 +') a union all select * from(' +@sql2 +') b union all select * from (' +@sql3 +') c' )
还是有错误: 如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。 这是什么原因。。???
我用查询分析器执行。。结果:select * from (SELECT top 2 * FROM danxuan WHERE courseID='czxt' and difficulty= '易' order by newid()) a union all select * from(SELECT top 2 * FROM danxuan WHERE courseID='czxt' and difficulty= '中' order by newid()) b union all select * from (SELECT top 2 * FROM danxuan WHERE courseID='czxt' and difficulty= '难' order by newid()) c 服务器: 消息 104,级别 15,状态 1,行 1 如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。
把单个查询语句后面的order by newid() 去掉的话就没问题了。。可是这边一定要用到order by 网上查了是 order by 和union冲突了。。哪位帮忙改下这个代码就是EXec里面的。。
CREATE PROCEDURE sp_getdxtInfobyDiff
@n1 int,
@n2 int,
@n3 int,
@courseId varchar(20)
AS
BEGINDECLARE @Sql1 varchar(8000),@Sql2 varchar(8000),@Sql3 varchar(8000)SET @Sql1 = 'SELECT top '+ cast(@n1 as varchar(20)) + '* FROM danxuan WHERE courseID='+ char(39) + @courseId + char(39) +' and difficulty= ''易'' order by newid()'
--EXEC(@Sql)
SET @Sql2 = 'SELECT top '+ cast(@n2 as varchar(20))+ '* FROM danxuan WHERE courseID='+ char(39) + @courseId + char(39) +' and difficulty= ''中'' order by newid()'
--EXEC(@Sql)
SET @Sql3 = 'SELECT top '+ cast(@n3 as varchar(20)) + '* FROM danxuan WHERE courseID='+ char(39) + @courseId + char(39) +' and difficulty= ''难'' order by newid()'EXEC('select * from ('
+@sql1
+') a union all select * from('
+@sql2
+') b union all select * from ('
+') c'
)END
GO
2005
CREATE PROCEDURE sp_getdxtInfobyDiff
@n1 int,
@n2 int,
@n3 int,
@courseId varchar(20)AS
BEGIN
with cte1 as(
select top (@n1) * FROM danxuan WHERE courseID= @courseId and difficulty= '易' order by newid()
)
,cte2 as(
select top (@n2) * FROM danxuan WHERE courseID= @courseId and difficulty= '中' order by newid()
)
,cte3 as(
select top (@n3) * FROM danxuan WHERE courseID= @courseId and difficulty= '难' order by newid()
)
select * from cte1
union all
select * from cte2
union all
select * from cte3
end
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。
--先把语句打印出来看看
print 'select * from ('
+@sql1
+') a union all select * from('
+@sql2
+') b union all select * from ('
+@sql3
+') c'--再执行
EXEC('select * from ('
+@sql1
+') a union all select * from('
+@sql2
+') b union all select * from ('
+@sql3
+') c'
)
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。
这是什么原因。。???
服务器: 消息 104,级别 15,状态 1,行 1
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。
网上查了是 order by 和union冲突了。。哪位帮忙改下这个代码就是EXec里面的。。