Oracle sql缺少右括号

发布于 2024-10-22 19:01:30 字数 576 浏览 4 评论 0原文

我试图限制运行查询时显示的行数。当我在 SQL Developer 中运行下面的代码时，它返回缺少右括号错误。

select * from
(select row_number()  over (order by rescode) rnum, a.* from
    (    
     SELECT * 
     FROM trans z 
     LEFT JOIN emails a 
     ON z.email1_hist_id=a.email_id 
     or z.email2_hist_id=a.email_id 
    ) a
)   where rnum between 1 and 50;

我尝试运行内部查询：

SELECT * 
FROM trans z 
LEFT JOIN emails a 
ON z.email1_hist_id=a.email_id 
or z.email2_hist_id=a.email_id

并且它工作正常。另一方面，我尝试删除查询的 OR 部分并包含限制行查询，它返回我指定的行数。

我的代码到底有什么问题？

原文

I'm trying to limit the number of rows that would be displayed when I run my query.
When I run the code below in SQL developer, it returns missing right parenthesis error..

select * from
(select row_number()  over (order by rescode) rnum, a.* from
    (    
     SELECT * 
     FROM trans z 
     LEFT JOIN emails a 
     ON z.email1_hist_id=a.email_id 
     or z.email2_hist_id=a.email_id 
    ) a
)   where rnum between 1 and 50;

I tried running the inside query:

SELECT * 
FROM trans z 
LEFT JOIN emails a 
ON z.email1_hist_id=a.email_id 
or z.email2_hist_id=a.email_id

and it works fine. On the other hand I tried removing the OR portion of my query and included the limit rows query and it returns the number of rows I specified.

What exactly is wrong in my code?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你是暖光i 2024-10-29 19:01:30

这应该可行 - 您不需要两级子查询

select *
from
(    SELECT *, row_number()  over (order by rescode) rnum
     FROM trans z 
     LEFT JOIN emails a 
     ON (z.email1_hist_id=a.email_id or z.email2_hist_id=a.email_id)
) x
where rnum between 1 and 50;

另外，请确保 trans 和 emails 之间没有重复的列名 - 这将使查询失败，因为* 不能返回重复的名称。

This should work - you don't need two levels of subquery

select *
from
(    SELECT *, row_number()  over (order by rescode) rnum
     FROM trans z 
     LEFT JOIN emails a 
     ON (z.email1_hist_id=a.email_id or z.email2_hist_id=a.email_id)
) x
where rnum between 1 and 50;

Also, make sure there are no duplicate column names between trans and emails - this will trip the query because * from the inner query cannot return duplicate names.

回复收藏 0 原文