日期:2014-05-17  浏览次数:20557 次

给变量赋值的select语句, 如何用不存在的字段排序?
SQL code

select top 5 c1, c2, c2 % c1 c3 
from t1
order by c3 desc


这样是没问题的

SQL code
declare @c1 int
declare @c2 int
declare @c3 int
select top 5 @c1=c1, @c2=c2, @c3=c2 % c1 c3
from t1
order by c3 desc

这样子就出问题了. 该如何解决?


------解决方案--------------------
SQL code

select top 1 @c1 = c1, @c2 = c2, @c3 = c2 % c1
from @t
order by c2 % c1 desc

------解决方案--------------------
SQL code

with cte as(
declare @c1 int
declare @c2 int
declare @c3 int
select top 5 @c1=c1, @c2=c2, @c3=c2 % c1 c3
from t1

)
select * from cte order by c3 desc

------解决方案--------------------
,,看错了参数接收不能用CTE
------解决方案--------------------
declare @c1 int
declare @c2 int
declare @c3 int
select top 5 @c1=c1, @c2=c2, @c3=c2 % c1 c3
from t1
order by c2 % c1desc

其实这样就行了


------解决方案--------------------
大哥,你这定义的是变量,不是表变量,top 5有5个值的。。