主页 / dba / 问题 / 71916
Accepted
brycemcd
Asked: 2014-07-20 17:42:13 +0800 CST

如何内连接一些表和外连接其他表？

  • 772

我敢肯定最终的答案会让我感到尴尬，但我一直在谷歌上搜索并用头撞墙，但无法弄清楚。

我有下面描述的四个表。我正在尝试获取显示与用户相关的所有产品（通过user_products表格）和所有类别的查询。

表定义

CREATE TEMPORARY TABLE categories (
   id INT PRIMARY KEY NOT NULL
   , cat_name TEXT
 );

 CREATE TEMPORARY TABLE products (
   id INT PRIMARY KEY NOT NULL
   , category_id INT
   , prod_name TEXT
 );

 CREATE TEMPORARY TABLE users (
   id INT PRIMARY KEY NOT NULL
   , user_name TEXT
 );

 CREATE TEMPORARY TABLE user_products (
   id INT PRIMARY KEY NOT NULL
   , product_id INT
   , user_id INT
   , quantity INT
 );

插入语句

INSERT INTO categories VALUES (1, 'cat1')
                             ,(2, 'cat2')
                             ,(3, 'cat3')
                             ,(4, 'cat4');

 INSERT INTO products VALUES (1, 1, 'prod1')
                            ,(2, 1, 'prod2')
                            ,(3, 2, 'prod3')
                            ,(4, 3, 'prod4')
                            ,(5, 4, 'prod4');

 INSERT INTO users VALUES (1, 'user1')
                         ,(2, 'user2')
                         ,(3, 'user3');

 INSERT INTO user_products VALUES (1, 1, 1, 4)
                                 ,(2, 2, 1, 3)
                                 ,(3, 3, 1, 3)
                                 ,(4, 2, 2, 3)
                                 ,(5, 2, 2, 2)
                                 ,(6, 5, 2, 2);

我已经尝试了几种选择，这确实感觉像是一个外部连接场景。最有前途但行不通的查询是：

SELECT up.*, p.*, c.*, u.*
FROM user_products AS up
  JOIN users AS u ON u.id = up.user_id
  JOIN products AS p ON p.id = up.product_id
  RIGHT OUTER JOIN categories AS c ON c.id = p.category_id
WHERE u.id = 1 OR u.id IS NULL

以上查询的结果

 id | product_id | user_id | quantity | id | category_id | prod_name | id | cat_name | id | user_name 
----+------------+---------+----------+----+-------------+-----------+----+----------+----+-----------
  1 |          1 |       1 |        4 |  1 |           1 | prod1     |  1 | cat1     |  1 | user1
  2 |          2 |       1 |        3 |  2 |           1 | prod2     |  1 | cat1     |  1 | user1
  3 |          3 |       1 |        3 |  3 |           2 | prod3     |  2 | cat2     |  1 | user1
    |            |         |          |    |             |           |  3 | cat3     |    |

它不起作用的原因是它无法从类别表中返回“cat4”。显然这是因为WHERE子句从结果集中选择它。我不知道如何获得一个结果集，其中包括所有类别 + 指定 user_products 表中有记录的产品和数量的详细信息。

查询应该

  • 返回所有类别
  • 如果用户拥有该产品，则返回为用户信息填充的数据（user_products 中存在一条记录）
  • 不返回具有其他用户 ID 的行
postgresql join

2 个回答

  1. Best Answer

    听起来您正在寻找一个FULL [OUTER] JOIN. 根据文档：

    全外连接

    首先，执行内部连接。然后，对于 T1 中不满足与 T2 中任何行的连接条件的每一行，在 T2 的列中添加具有空值的连接行。此外，对于 T2 中不满足与 T1 中任何行的连接条件的每一行，添加一个在 T1 的列中具有空值的连接行。

    SELECT *
FROM   users           u  
JOIN   user_products   up ON u.id = up.user_id
                         AND u.id = 1
JOIN   products        p ON p.id = up.product_id
FULL   JOIN categories c ON c.id = p.category_id;

    这将返回所有类别，还返回给定 的所有用户-产品组合user_id。

    如果您使用外键强制参照完整性并products.category_id定义为NOT NULL，以便将每个产品分配给现有类别（不在您的问题中），您可以将 替换FULL JOIN为RIGHT JOIN.

    另外，无论哪种方式，选择特定用户的条件都必须移至子查询或（更简单）移至JOIN前三个表之间的条件。它必须在加入最后一个表之前categories应用。

    SQL小提琴。

    • 3

  2. 我相信你想显示所有类别。

    如果是这样，您将需要使所有连接都成为 RIGHT OUTER JOINS。

    为什么？

    1. 外连接运算符的关联性和优先级定义不明确且不可预测
    2. 开始后，您需要继续“向外”。因为您使用的是 RIGHT 联接，所以前面的每个联接也必须是 RIGHT 联接。否则，外部表中的 NULL 将永远不会加入产品用户或用户表，这也会切断您的类别

    请注意，您可能会看到用户重复使用，因为用户可以与多个类别的多个产品相关联。

    祝你好运！

    安德鲁沃尔夫

    • 0

相关问题