Oracle左外连接:如何限制右表中的请求

发布于 2024-09-18 14:01:48 字数 377 浏览 7 评论 0原文

我有一个很大的声明:

SELECT
    a.user_id, a.user_name,
    s.name, s.value,
    d.default
FROM
    accounts a,
    settings s
    LEFT OUTER JOIN default d ON ( d.name = s.name )
WHERE
    s.user_id = a.user_id;

问题是 settings 包含大量条目,我需要选择 ID 最高的条目。我可以想象更改语句并用子选择替换连接(它从 settings 获取正确的条目),但我很好奇是否有更好的解决方案。任何意见都会受到赞赏。非常感谢!

I have a large statement:

SELECT
    a.user_id, a.user_name,
    s.name, s.value,
    d.default
FROM
    accounts a,
    settings s
    LEFT OUTER JOIN default d ON ( d.name = s.name )
WHERE
    s.user_id = a.user_id;

The problem is that settings contains a large amount of entries and I need to pick the one with the highest ID. I can imagine do change the statement and replace the join with a subselect (which grabs the correct entry from settings), but I'm curious to see if there is a better solution. Any input is appreciated. Many thanks!

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

扫码二维码加入Web技术交流群

发布评论

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

评论(2

不气馁 2024-09-25 14:01:48

您可以使用子查询从右表中获取所需的结果。

像这样的东西:

SELECT
    *
FROM
    accounts a,
    (
      SELECT
          user_id,
          *
      FROM
          settings
      WHERE
          RANK() OVER (ORDER BY id DESC, PARTITION BY user_id) = 1
    ) s

You can use a sub-query to get just the results you want out of the right table.

Something like:

SELECT
    *
FROM
    accounts a,
    (
      SELECT
          user_id,
          *
      FROM
          settings
      WHERE
          RANK() OVER (ORDER BY id DESC, PARTITION BY user_id) = 1
    ) s
美人骨 2024-09-25 14:01:48

从表中获取最高的 ID 可以使用 a

select max(id) ...

或 with a

select id from settings where rownum=1 order by id desc

来完成(我更喜欢第一个解决方案)
或者就像约翰提议的那样,但无论如何你都需要一个子查询。
另一件事,您的示例中可能存在一些拼写错误,我不知道 d 来自哪里,也没有进行自动左连接的要点...

这是我会写的

SELECT
    user_id, user_name,
    name, value,
    default
FROM
    accounts join 
    (select user_id,name 
     from settings 
     where RANK() OVER (ORDER BY id DESC, PARTITION BY user_id) = 1) using(user_id)
    LEFT OUTER JOIN default using ( name )

...仍然是子查询

getting the highest ID from a table could be done with a

select max(id) ...

or with a

select id from settings where rownum=1 order by id desc

(i prefer the first solution)
or just like John proposed, but you'll need a subquery anyway.
One more thing, there might be some typo in your example, i don't see where d is coming from, neither the point of making an auto left join...

Here is what I would have written

SELECT
    user_id, user_name,
    name, value,
    default
FROM
    accounts join 
    (select user_id,name 
     from settings 
     where RANK() OVER (ORDER BY id DESC, PARTITION BY user_id) = 1) using(user_id)
    LEFT OUTER JOIN default using ( name )

... still subquery

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文