mysql - SQL：如何获取表的列表对

Question

要求：列出经常光顾同一酒吧的顾客对（即列出可能在咖啡吧遇到的所有顾客对）

吧台：

-------------------------
id| name                 
-------------------------
1 | Vivamus nibh         
2 | odio tristique       
3 | vulputate ullamcorper
4 | Cras lorem           
5 | libero est,          

客户表：

-----------------------
id| name              
-----------------------
1 | Warren    
2 | Olympia            
3 | Logan
4 | Summer
5 | Kamal
6 | Fernandez

频繁表：

-----------------
cust_id | bar_id
-----------------
1       | 1
2       | 1
3       | 2
4       | 2
5       | 3
6       | 4

预期输出：

---------------------------------------
customer1 | customer2 | barname
---------------------------------------
Warren    |  Olympia  | Vivamus nibh
Logan     |  Summer   | odio tristique

这是我的尝试，但没有奏效：

select c1.name, c2.name, b1.name, b2.name
from frequents f1, frequents f2
join bar b1 on f1.bar_id = b1.id
join bar b2 on f2.bar_id = b2.id
join customer c1 on f1.cust_id = c1.id
join customer c2 on f2.cust_id = c2.id
where f1.bar_id = f2.bar_id;

标签： mysqlsqlselect

Answer 1

您可以将条形表与频繁表连接两次，然后继续连接以获取客户名称。为了防止重复，您可以任意决定一个cust_id应该小于另一个：

SELECT b.name, c1.name, c2.name
FROM   bar b
JOIN   frequents f1 ON f1.bar_id = b.id
JOIN   frequents f2 ON f2.bar_id = b.id AND f1.cust_id < f2.cust_id
JOIN   customer  c1 ON c1.id = f1.cust_id
JOIN   customer  c2 ON c2.id = f2.cust_id

DBFiddle