对一个联接使用第一个结果在第二个结果（子查询）来获取所有行的一个查询

提问者：小点点

对一个联接使用第一个结果在第二个结果（子查询）来获取所有行的一个查询

所以我在这个问题上纠结了一段时间。虽然我确实可以对这个问题进行两个单独的查询，但我想知道是否可以进行一个查询。我认为一个SQL专业人士肯定知道如何做到这一点。所以事情是这样的:

我们有两个表posts和post_translations。为简单起见缩写。

posts
------
id

post_translations
-----------------
id
post_id (which is the FK on posts...)
locale
slug
content
... (and so on)

很明显，我现在可以做一个非常简单的内部连接，把所有的帖子翻译成一种特定的语言，我们可以说'en'或'de'，如果你想的话。所以我不会再为这事打扰你了。

但由于表还将包含子区域设置，如en（美国），en-GB，en-AU，de（德国），de-AT，de-CH.。。。随着Szenario的到来，整个事情变得更加复杂。

假设有些帖子只翻译成“de”，也有翻译成“de-at”的。该表将如下所示:

post_translations
id    post_id   locale    content
1        1       de         ...
2        1       de-AT      ...
3        2       de         ...

所以post 1在“de”和“de-at”中可用。岗位2仅在“de-at”中可用。

如果我想把所有的帖子都放在“de”里，那就很容易了。我只需要添加一个WHERE locale='de'就可以了。但是，假设我希望所有的帖子都在'de-at'中，所有其他的帖子都在'de'中，但没有翻译成'de-at'，这样我就不会得到任何重复的内容--我如何在一个查询中实现这一点呢？如前所述，我可以在这里运行两个查询，首先在'de-at'中获取所有的post，然后在'de'中获取所有的post，并使用我从第一个查询中获得的'post_id'和一个WHERE not in query，这样我就不会得到任何重复。

这些询问将是:

SELECT 
    pt.post_id
FROM
    posts AS p
        INNER JOIN
    post_translations AS pt ON p.id = pt.post_id
WHERE
    pt.locale = 'de-AT';

在此查询中，您将使用post_id:

SELECT 
    *
FROM
    posts AS p
        INNER JOIN
    post_translations AS pt ON p.id = pt.post_id
WHERE
    pt.locale = 'de' AND pt.post_id NOT IN (*post_ids found in first search*);

查询背后的思想是显示一个地区的特定帖子，在本例中是“de-at”，但也显示为“de”用户编写的一般帖子。

我希望这说得通。如果你能帮上忙，我会很感激的。谢谢。

共2个答案

匿名用户

一个选项使用不存在:

select pt.*
from post_translations pt
where 
    locale = 'de-AT'
    or (
        locale = 'de'
        and not exists (
            select 1 
            from post_translations pt1
            where pt1.post_id = pt.post_id and pt1.locale = 'de-AT'
        )
    )

或者，如果您正在运行MySQL8.0，也可以使用row_number():

select *
from (
    select 
        pt.*, 
        row_number() over(partition by post_id order by (locale = 'de')) rn
    from post_translations pt
    where locale in ('de', 'de-AT')
) pt
where rn = 1

您可以轻松地将上述查询修改为joinposts表。

匿名用户

显示一个地区的特定帖子，在本例中为“de-at”，但也显示为“de”用户编写的一般帖子

我相信简单的where子句和运算符in将返回预期的结果。
然后，您可以使用条件聚合标记具有所需的子区域设置的帖子，并可能首先对这些帖子进行排序:

SELECT post_id, 
       MAX(locale = 'de-AT') AS flag
FROM post_translations 
WHERE locale IN ('de', 'de-AT')
GROUP BY post_id
ORDER BY flag DESC

您可以将上述查询连接到posts以获取每个post的详细信息:

SELECT p.*, t.flag
FROM posts AS p INNER JOIN (
    SELECT post_id, 
           MAX(locale = 'de-AT') AS flag
    FROM post_translations 
    WHERE locale IN ('de', 'de-AT')
    GROUP BY post_id
) t ON t.post_id = p.id
ORDER BY t.flag DESC