重构 SQL（解决方法 RIGHT OUTER JOIN）

发布于 2024-07-13 03:02:31 字数 702 浏览 9 评论 0原文

由于 SQLite 不支持 RIGHT OUTER JOINS，我提出以下挑战（阅读：邀请为我做我的工作）：

重构此查询，使其不再使用 SQLite 不支持的结构，如 RIGHT/FULL OUTER JOIN。

SELECT     strings.*, translations.text
FROM         translations INNER JOIN
                      language ON translations.language_id = language.id RIGHT OUTER JOIN
                      strings ON translations.string_id = strings.id
WHERE     (language.handle = 'english')

我感觉它可以通过子查询或通过旋转表并执行 LEFT OUTER JOIN 来实现，但我的尝试失败了；我的 SQL 不再像以前那样了。

以下是显示适用架构的查询生成器大纲：http://dl.getdropbox。 com/u/264612/sql-refactor.PNG

首先破解它，得到 dekz 的电子拥抱

原文

Since SQLite does not support RIGHT OUTER JOINS I pose the following challenge (read: invitation to do my work for me):

Refactor this query so it no longer utilises SQLite-unsupported constructs like RIGHT/FULL OUTER JOINs.

SELECT     strings.*, translations.text
FROM         translations INNER JOIN
                      language ON translations.language_id = language.id RIGHT OUTER JOIN
                      strings ON translations.string_id = strings.id
WHERE     (language.handle = 'english')

I sense it can be achieved with subqueries or by pivoting the tables and performing a LEFT OUTER JOIN but my attempts have failed; my SQL's not what it used to be.

Here's a query builder outline showing the applicable schema: http://dl.getdropbox.com/u/264612/sql-refactor.PNG

First to crack it gets an e-hug from dekz

分享到QQ

分享到微博

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

发布评论

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

吹泡泡o 2024-07-20 03:02:31

以下未经测试。

select strings.*, translations.text
from strings left outer join translations
     on translations.string_id = strings.id
           and translations.language_id = (select id
                                           from language
                                           where language.handle = 'english')

我认为这将为您提供具有匹配翻译文本的所有字符串，其中存在合适的英语翻译。这就是你想要得到的吗？

The following is untested.

select strings.*, translations.text
from strings left outer join translations
     on translations.string_id = strings.id
           and translations.language_id = (select id
                                           from language
                                           where language.handle = 'english')

I think this will give you all strings with the matching translation text where a suitable translation exists in English. Is that what you are trying to get?

回复收藏 0 原文

断桥再见 2024-07-20 03:02:31

有趣的是，SQLite 允许 LEFT OUTER JOIN，但不允许 RIGHT OUTER JOIN。好吧，因为它确实允许 LEFT OUTER JOIN，所以你是对的，你可以重新排列连接顺序：

SELECT     strings.*, translations.text
FROM strings LEFT OUTER JOIN (
    translations INNER JOIN language ON translations.language_id = language.id
) tr ON tr.string_id = strings.id
WHERE     (language.handle = 'english')

[编辑：应用了 Blorgbeard 命名连接表以获取要解析的查询的建议 - 希望它现在可以工作！ ]

Intriguing that SQLite allows LEFT OUTER JOINs but not RIGHT OUTER JOINs. Well, since it does allow LEFT OUTER JOINs, you're right, you can just rearrange the join order:

SELECT     strings.*, translations.text
FROM strings LEFT OUTER JOIN (
    translations INNER JOIN language ON translations.language_id = language.id
) tr ON tr.string_id = strings.id
WHERE     (language.handle = 'english')

[EDIT: Applied Blorgbeard's suggestion of naming the joined table to get the query to parse -- hope it works now!]

回复收藏 0 原文

~没有更多了~