如何解决"No column was specified for column 1 of 'r'"

Question

我的SQL查询是

select *
from (
select rtrim(Emp.s_filterh)
from Emp, Dept
where Dept.label = 'xyz' and Emp.filtery = 1 and Emp.r_id = Dept.r_id  
order by 1 desc offset 0 rows
) as r
where ROW_NUMBER() < 26

当我运行这个时，我得到

'r'的列1没有指定列

当我读到错误时，我们应该提供别名，但在我的情况下，即使提供别名查询也是抛出错误。

编辑:我试图使用ROW_NUMBER()来限制获取的行数。

谁能告诉我出了什么问题

Answer 1

为您的列指定别名，如下所示:

select * from (select rtrim(Emp.s_filterh) my_column_name from Emp, Dept
where Dept.label = 'xyz' and Emp.filtery=1 and Emp.r_id=Dept.r_id  
order by 1 desc offset 0 rows ) as r
where ROW_NUMBER < 26 

注意:始终使用标准的ANSI连接。

Answer 2

如果您想使用ROW_NUMBER来检索前26行，那么使用以下语法:

SELECT *
FROM
(
SELECT *, RTRIM(Emp.s_filterh),
ROW_NUMBER() OVER (ORDER BY RTRIM(Emp.s_filterh) DESC) rn
FROM Emp e
INNER JOIN Dept e ON d.r_id = e.r_id
WHERE d.label = 'xyz' AND e.filtery = 1
) t
WHERE rn < 26;