MYSQL 查询(带有连接和虚拟值)

Question

我有 3 个 InnoDB 表:电子邮件、网站和订阅。

emails 表包含 id 和 email 列。

网站表具有id和address列。

subscriptions 表包含 id、email_id 和 website_id 列。

我想做的是提供一封电子邮件并返回一个包含地址列和订阅列的表格。前者是 websites 表中所有地址的列表，如果提供的电子邮件地址在带有 website_id 的 subscriptions 表中出现，则后者的值为 1 设置为该网站，否则设置为 0。但即使找不到用户，我也愿意保留所有网站。

我遇到的问题是，当该电子邮件有该记录时，我应该将虚拟列 subscribed 的值从 0 更改为 1。

这是我到目前为止的查询。有人知道该怎么做吗？

SELECT `address`, "0" AS `subscribed`
/* 0 becomes 1 for the websites email has subscribed to */
FROM `websites` a
LEFT JOIN (
    SELECT s.`website_id` FROM `subscriptions` s
    RIGHT JOIN (
        SELECT `id` AS `email_id` FROM `emails`
        WHERE `email`='someone@mail.com' LIMIT 1) e
    ON s.`email_id`=e.`email_id`) l
ON l.`website_id`=a.`id`

以下是 subscribed 列所需值的示例输出:

• 如果在 emails 表中找不到电子邮件，则所有行的值均为 0
• 如果在 emails 表中找到电子邮件...
  • 如果在 subscriptions 表中找不到，则所有行的值均为 0
  • 如果在 subscriptions 表中找到，相应的地址行将获取值 1

如果我不能很好地解释它，请告诉我。有人知道我应该在查询中更改什么吗？

提前致谢。

Answer 1

SELECT w.address, CASE WHEN s.id IS NULL THEN 0 ELSE 1 END AS subscribed
    FROM websites w
        LEFT JOIN subscriptions s
            INNER JOIN emails e
                ON s.email_id = e.id
                    AND e.email = 'someone@mail.com'
            ON w.id = s.website_id

您也可以通过这种方式得出订阅值，这更简洁，但也不太明显。

SELECT w.address, COALESCE(s.id/s.id, 0) AS subscribed
    FROM websites w
        LEFT JOIN subscriptions s
            INNER JOIN emails e
                ON s.email_id = e.id
                    AND e.email = 'someone@mail.com'
            ON w.id = s.website_id