最近想把分页改成row_number() over的方式,碰到个问题原来的SQL语句如下:
select case when [a]='大' then 1 else 0 end as [a_temp],[b],[c] from [table] order by [a_temp] desc
这里我自定义了一个临时列 [a_temp]加入row_number() over:
select case when [a]='大' then 1 else 0 end as [a_temp],[b],[c],row_number() over(order by [a_temp] desc) as [rowid] from [table]
这时候就会报列名 'a_temp' 无效了,我知道这是肯定的,但是用了row_number() over后order by只能写在over里,否则排出来的顺序不对,咋办呢?
select case when [a]='大' then 1 else 0 end as [a_temp],[b],[c] from [table] order by [a_temp] desc
这里我自定义了一个临时列 [a_temp]加入row_number() over:
select case when [a]='大' then 1 else 0 end as [a_temp],[b],[c],row_number() over(order by [a_temp] desc) as [rowid] from [table]
这时候就会报列名 'a_temp' 无效了,我知道这是肯定的,但是用了row_number() over后order by只能写在over里,否则排出来的顺序不对,咋办呢?
case when [a]='大' then 1 else 0 end as [a_temp],[b],[c],
row_number() over(order by case when [a]='大' then 1 else 0 end desc) as [rowid]
from
[table]
(
select case when [a]='大' then 1 else 0 end as [a_temp],[b],[c] from [table]
) m