组合左外部和内部连接+聚合函数-空结果集问题

Question

这可能很简单，但我目前没有看到它。试图结合左外连接和内连接，以便从给定的一组表中获取任何可用的信息，所有这些信息都与customer_id

相关示例可能不是完美的设计(我根据我的实际查询组成)，但应该足以说明我的问题，这是一个空的结果集，即使其中一些表中有行。

示例表:

简介:

id_profile   nm_profile 
-----------  ----------  
1234         User profile     

订单:

id_order id_customer order_date    order_type
-------  ----------  ---------     ----------     
10308    1234        2017-09-18    Online
10309    1234        2018-09-18    Online 

评论:

id_review  id_profile   id_order   text    score   
---------  ----------   --------   -----   ------
(no rows for this id_profile)

查询:

SELECT c.id_customer, MIN(o.order_date) order_date, r.text review_text
FROM Customer c
JOIN Profile p ON c.id_customer = p.id_profile 
LEFT OUTER JOIN Orders o ON o.id_customer = c.id_customer AND o.order_type = 'Online'
LEFT OUTER JOIN Reviews r ON r.id_reviewer = p.id_profile AND r.score = 5
WHERE c.id_customer = 1234
GROUP BY c.id_customer

假设这些列匹配，并且我能够运行上面的查询，我试图实现以下目标:

id_customer    order_date   review_text
-----------    ----------   -----------
1234           2017-09-18   <NULL>

这是一个更大查询的一部分;试图将其分解为最基本的表达，以了解我可能做错了什么。我试过在连接中避免WHERE子句，也试过LEFT OUTER JOIN (SELECT ....)，但是没有运气。

提前感谢!

Answer 1

您应该在聚合完成后加入Reviews:

SELECT t.id_customer, t.order_date, r.text review_text
FROM ( 
SELECT c.id_customer, MIN(o.order_date) order_date
FROM Customer c
INNER JOIN Profile p ON c.id_customer = p.id_profile 
LEFT JOIN Orders o ON o.id_customer = c.id_customer AND o.order_type = 'Online'
WHERE c.id_customer = 1234
GROUP BY c.id_customer
) t
LEFT JOIN Reviews r ON r.id_reviewer = t.id_customer AND r.score = 5;

Answer 2

如果您只想要包含最大订单日期的所有评论信息，那么您可以汇总orders表并加入其余的:

SELECT c.id_customer, o.min_order_date, r.text as review_text
FROM Customer c JOIN
Profile p ON c.id_customer = p.id_profile LEFT JOIN
(SELECT o.id_customer, MIN(o.order_date) as min_order_date
FROM Orders o
WHERE o.order_type = 'Online'
GROUP BY o.id_customer
) o
ON o.id_customer = c.id_customer LEFT JOIN 
Reviews r
ON r.id_reviewer = p.id_profile AND r.score = 5
WHERE c.id_customer = 1234;

如果id_customer有多个评论，这将返回多个行。你的问题没有提到如何处理。