显示所有组合的 SQL 连接

Question

我想要一个“连接”，它可以让我知道每个类别（cat）和每个站点（cat_sites）是否彼此关联具有

CREATE TABLE cat (
  cat varchar(34) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE cat_sites (
  cat varchar(34) CHARACTER SET utf8 DEFAULT NULL,
  site varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

以下值：

INSERT INTO `cat` VALUES (''),('Autobus - Autocar'),('Camion / Tracteur de semi-remorque'),('Camionette'),('Motocycle'),('Remorque <3,5'),('Remorque >3,5'),('Tracteur'),('Tricycle / Quadricycle'),('Véhicule spécial'),('Voiture');

INSERT INTO `cat_sites` VALUES ('','EA'),('','MA'),('','SW'),('Autobus - Autocar','BI'),('Autobus - Autocar','LI'),('Autobus - Autocar','SW'),('Camion / Tracteur de semi-remorque','BI'),('Camion / Tracteur de semi-remorque','EA'),('Camion / Tracteur de semi-remorque','LI'),('Camion / Tracteur de semi-remorque','MA'),('Camion / Tracteur de semi-remorque','SW'),('Camionette','BI'),('Camionette','EA'),('Camionette','LI'),('Camionette','MA'),('Camionette','SW'),('Motocycle','BI'),('Motocycle','EA'),('Motocycle','MA'),('Motocycle','SW'),('Remorque <3,5','BI'),('Remorque <3,5','EA'),('Remorque <3,5','LI'),('Remorque <3,5','MA'),('Remorque <3,5','SW'),('Remorque >3,5','BI'),('Remorque >3,5','EA'),('Remorque >3,5','LI'),('Remorque >3,5','SW'),('Tracteur','BI'),('Tracteur','EA'),('Tracteur','LI'),('Tracteur','MA'),('Tracteur','SW'),('Tricycle / Quadricycle','BI'),('Tricycle / Quadricycle','EA'),('Tricycle / Quadricycle','MA'),('Tricycle / Quadricycle','SW'),('Véhicule spécial','BI'),('Véhicule spécial','LI'),('Voiture','BI'),('Voiture','EA'),('Voiture','LI'),('Voiture','MA'),('Voiture','SW');

cat：

cat_sites:

以下查询几乎正是我正在寻找的内容：

SELECT * FROM cat  LEFT JOIN cat_sites ON cat.cat = cat_sites.cat

问题是，我确实获得了所有类别，但只有实际与给定类别相关联的网站。例如，“Autobus - Autocar”仅在“BI”、“LI”和“SW”中可用，但在“EA”和“MA”中不可用。

我想看到的是 c1="Autobus - Autocar" 的记录以及 c2 为空值的缺失站点。

我可以通过传递中间查询来获得结果，该查询将为我提供所有可能的组合：

SELECT cat.cat, site.site FROM cat 
RIGHT OUTER JOIN (select distinct site from cat_sites) AS site ON 1=1

最终的查询将是这样的：

SELECT cat_allsites.cat c1, cat_allsites.site, cat_sites.cat c2
FROM
(
SELECT cat.cat, site.site FROM cat 
RIGHT OUTER JOIN (select distinct site from cat_sites) AS site ON 1=1
) as cat_allsites
LEFT JOIN cat_sites ON cat_allsites.cat = cat_sites.cat

WHERE cat_allsites.cat="Autobus - Autocar"
group by cat_allsites.cat , cat_allsites.site, cat_sites.cat 
;

有人对如何执行此操作有更好的想法吗？

Answer 1

您通常会交叉连接两个集合来查找所有可能的组合，然后使用左连接来查找匹配/缺失值：

select cat.cat, sites.site, cat_sites.*
from cat
cross join (select distinct site from cat_sites) as sites
left join cat_sites on cat.cat = cat_sites.cat and sites.site = cat_sites.site