如何用基于第三个连接表的条件连接第二个表?

Question

我需要将 2 个表与第二个表与第二个表连接，该表具有基于 3 个 JOIN 表的 JOIN 条件。

问题是，我无法从第三个 JOIN 表中获取值，同时仍然连接第二个表。

在DB Fiddle上查看

Table A: users
-------
user_id INT PRIMARY

Table B: orders
-------
order_id INT PRIMARY
user_id INT
invoice_id INT

Table C: invoices
-------
invoice_id INT PRIMARY
invoice_status VARCHAR [voided, paid]

这就是我希望做的：

SELECT 
A.user_id,
B.order_id,
C.invoice_id,
C.invoice_status
FROM users A
LEFT JOIN orders B 
ON (B.user_id = A.user_id
AND C.invoice_status = 'paid')
LEFT JOIN invoices C
ON (C.invoice_id = B.invoice_id)

第 9 行AND C.invoice_status = 'paid')指的是尚未连接的表 C，因此此查询将导致Error in query (1054): Unknown column 'C.invoice_status' in 'on clause'

我不能在WHERE条款中设置此条件的原因是，我仍然想返回所有用户记录，无论他们是否有任何订单或"已付款"发票。因此，添加WHERE invoice_status = 'paid'不会返回没有任何订单的用户和invoice_status= "voided" 的用户。

此外，当用户有两个订单链接到两个不同的发票记录，但一张发票的invoice_status是"已付款"的，而另一张发票是"无效的"，我只想返回"已付款"记录。可以有很多voided发票，但只有 1 条"已付款"发票记录。

顺便说一下，这是强制执行的：sql_mode=only_full_group_by，所以如果没有对order_id&invoice_id字段进行某种聚合或条件，我就无法GROUP BY user_id。

<小时 />

SELECT 
A.user_id,
B.order_id,
C.invoice_id,
C.invoice_status
FROM users A
LEFT JOIN orders B 
ON (B.user_id = A.user_id)
LEFT JOIN invoices C
ON (C.invoice_id = B.invoice_id);

1

user_id	order_id invoice_id		invoice_status
1	1	无效
1	2 2	付费
2	3 1		无效
3	空

使用ROW_NUMBER()函数并根据user_id对行进行编号并根据invoice_status进行排序，然后获取第一行

SELECT user_id,
order_id,
invoice_id,
invoice_status
FROM
(SELECT 
A.user_id,
B.order_id,
C.invoice_id,
C.invoice_status,
ROW_NUMBER() OVER(PARTITION BY A.user_id ORDER BY C.invoice_status) AS num
FROM users A
LEFT JOIN orders B 
ON (B.user_id = A.user_id)
LEFT JOIN invoices C
ON (C.invoice_id = B.invoice_id)) t
WHERE num = 1

数据库<>小提琴

由于ProGu的评论，我能够通过使用EXISTS来解决它。

SELECT 
A.user_id,
B.order_id,
C.invoice_id,
C.invoice_status
FROM users A
LEFT JOIN orders B 
ON B.user_id = A.user_id
AND EXISTS( SELECT 
1
FROM
invoices
WHERE
invoices.invoice_id = B.invoice_id
AND invoices.invoice_status = 'paid')
LEFT JOIN invoices C
ON C.invoice_id = B.invoice_id

空 空

user_id	order_id invoice_id		invoice_status
1	2 2	付费
2		空 空
3	空