为什么 SQL 错误 ORA-00907: 缺少右括号

发布于 2025-01-17 02:28:59 字数 698 浏览 4 评论 0原文

这是我的查询：

SELECT person_name
  FROM travel_card
 WHERE id IN
       (SELECT travel_card_id
          FROM travel_payment
         WHERE entry_station_id IN
               (SELECT id
                  FROM station
                 WHERE name = 'Marina Bay MRT Station'
                    OR exit_station_id IN
                       (SELECT id
                          FROM station
                         WHERE name = 'Marina Bay MRT Station'))) I
 ORDER BY travel_card.person_name

为什么我会收到此错误？

SQL 错误：来自 name='滨海湾地铁站'的车站）I)
第 4 行错误：ORA-00907：缺少右括号

原文

Here is my query:

SELECT person_name
  FROM travel_card
 WHERE id IN
       (SELECT travel_card_id
          FROM travel_payment
         WHERE entry_station_id IN
               (SELECT id
                  FROM station
                 WHERE name = 'Marina Bay MRT Station'
                    OR exit_station_id IN
                       (SELECT id
                          FROM station
                         WHERE name = 'Marina Bay MRT Station'))) I
 ORDER BY travel_card.person_name

Why do I get this error?

SQL ERROR: from station where name='Marina Bay MRT Station')I)
ERROR at line 4: ORA-00907: missing right parenthesis

分享到QQ

分享到微博

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

发布评论

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

杀お生予夺 2025-01-24 02:28:59

格式化代码是能够阅读代码并发现错误的第一步，很明显存在一些错误。

我认为这就是您要写的内容：

select person_name
from travel_card
where id in (
    select travel_card_id
    from travel_payment
    where entry_station_id = (
        select id
        from station
        where name = 'Marina Bay MRT Station')
    or exit_station_id = (
        select id
        from station
        where name = 'Marina Bay MRT Station')
)
order by travel_card.person_name

请注意， = 替换了用于电台查找的 in ，并且我取消了子查询的嵌套。

您可以（并且应该）仅使用联接来表达这一点，但我将结构保留为与您的查询类似，因此差异最小化。

Formatting your code is the first step to being able to read it and find bugs, and it's clear there are some bugs.

I think this is what you meant to write:

select person_name
from travel_card
where id in (
    select travel_card_id
    from travel_payment
    where entry_station_id = (
        select id
        from station
        where name = 'Marina Bay MRT Station')
    or exit_station_id = (
        select id
        from station
        where name = 'Marina Bay MRT Station')
)
order by travel_card.person_name

Notice that = replaced in for the station lookups and I un-nested the subqueries.

You could (and should) express this using only joins, but I left the structure as similar to your query so the differences were minimised.

回复收藏 0 原文

挽你眉间 2025-01-24 02:28:59

IN 运算符后面的子查询不能使用别名，否则

如果别名嵌套在括号中，您将得到
ORA-00907
-> ))I)
ORA-00933 如果别名跟在所有括号后面
-> )))我

考虑您当前的情况。

似乎需要整理查询，并且大概您想将其编写为以下内容：

SELECT person_name
  FROM travel_card
 WHERE id IN
       (SELECT travel_card_id
          FROM travel_payment tp
          JOIN station s
            ON s.id IN (tp.entry_station_id,tp.exit_station_id)
         WHERE s.name = 'Marina Bay MRT Station')
 ORDER BY person_name;

但是，可能会更短：

SELECT DISTINCT tc.person_name
  FROM travel_payment tp
  JOIN station s
    ON s.id IN (tp.entry_station_id, tp.exit_station_id)
  JOIN travel_card tc
    ON tc.person_name = tp.travel_card_id
 WHERE s.name = 'Marina Bay MRT Station'
 ORDER BY tc.person_name

A subquery following an IN operator cannot be aliased, otherwise you'd get

ORA-00907 if the alias is nested in parentheses
-> ))I)
ORA-00933 if the alias follows all of the parentheses
-> )))I

considering your current case.

Seems that need to tidy up the query and presumably you want to write it as the following :

SELECT person_name
  FROM travel_card
 WHERE id IN
       (SELECT travel_card_id
          FROM travel_payment tp
          JOIN station s
            ON s.id IN (tp.entry_station_id,tp.exit_station_id)
         WHERE s.name = 'Marina Bay MRT Station')
 ORDER BY person_name;

yet, might be shorter :

SELECT DISTINCT tc.person_name
  FROM travel_payment tp
  JOIN station s
    ON s.id IN (tp.entry_station_id, tp.exit_station_id)
  JOIN travel_card tc
    ON tc.person_name = tp.travel_card_id
 WHERE s.name = 'Marina Bay MRT Station'
 ORDER BY tc.person_name

回复收藏 0 原文

~没有更多了~