时间戳

首页 > 解决方案 > 如何在三个表上使用 LEFT JOIN 和 WHERE？

mysql - 如何在三个表上使用 LEFT JOIN 和 WHERE？

问题描述

在 MySQL 5.7.32 中，我想从产品表中检索所有产品，如果它们可用，则从多个商店中获取这些产品的价格，如果它们不可用，则应为该商店的缺失价格返回 NULL。

共有三张表：产品、价格、商店

表：

-- table shops
id  | name
1   | amazon
2   | ebay
3   | craigslist

-- table products
ARTICLE_ID  | name
123         | article 1
124         | article 2
125         | article 3

-- table prices
DATE    | SHOP_ID       | ARTICLE_ID    |  PRICE
201220  | 1             | 123           | 12.99
201220  | 2             | 123           | 9.99
201220  | 1             | 124           | 10.80
201221  | 1             | 123           | 13.99

-- Desired result
SHOP        | PRODUCT | PRICE
amazon      | 123     | 12.99
amazon      | 124     | 10.80
amazon      | 125     | NULL
ebay        | 123     | 9.99
ebay        | 124     | NULL
ebay        | 125     | NULL

SQL：

SELECT
    s.name,
    p.id,
    mps.price
FROM
    shops s,
    products p
    LEFT JOIN prices mps ON p.ARTICLE_ID = mps.ARTICLE_ID AND mps.DATE = 20201220
WHERE
    s.ID IN ( '1' , '2')

我不知道如何为每个产品和商店显示一条线。这甚至可以通过该表设置实现，还是我需要更改我的数据库结构？

标签： mysql

解决方案

在该ON子句中，您还必须定义 and 之间的shops链接prices：

SELECT
    s.name,
    p.ARTICLE_ID,
    mps.price
FROM
    shops s CROSS JOIN products p
    LEFT JOIN prices mps 
    ON s.id = mps.shop_id AND p.ARTICLE_ID = mps.ARTICLE_ID  AND mps.DATE = 201220
WHERE
    s.ID IN ( '1' , '2')
ORDER BY s.name, p.ARTICLE_ID

见演示。
结果：

> name   | ARTICLE_ID | price
> :----- | ---------: | ----:
> amazon |        123 | 12.99
> amazon |        124 |  10.8
> amazon |        125 |  null
> ebay   |        123 |  9.99
> ebay   |        124 |  null
> ebay   |        125 |  null

推荐阅读