SQL: 4个表到1个表，包含计数、组和扣减？！

Question

我在机场有一个丢失、发现和匹配行李的项目。我是用Java(FX)和mySQL制作的。

这就是我有的:我有四张桌子：

1个有2列的表机场：

Airport_id & Airport_name

1表中有3列：

Found_id & Found_AirportID & Matched

1张表丢失3栏：

Lost_id & Lost_AirportID & Matched

1表与3列匹配：

Match_id & Match_LostID & Match_FoundID & Match_AirportID

每当进行匹配时，match表将获得一个新行，其中Match_LostID (来自Lost_id) & Match_FoundID (来自Found_id)和Match_AirportID (Found_AirportID)匹配(无论是查找还是丢失)都被设置为1，而不是NULL。

所有的机场to都链接到机场表。

我想要的；对于每一个机场，我要的是丢失物品的数量，找到的物品的数量和匹配的物品的数量。但是当一个项目被“匹配”时，它可能不会出现在失物招领处的计数中。

所以我想要一个4列的表:机场名称，找到的计数，丢失的计数，匹配的计数。

我提出了以下查询：

SELECT vv.Airport_name, 
COUNT(DISTINCT gb.Found_id) countFound, 
COUNT(DISTINCT vb.Lost_id) countLost, 
COUNT(DISTINCT kt.Match_id) countMatch
FROM Airports vv
LEFT JOIN Found gb ON vv.Airport_id = gb.Found_AirportID
LEFT JOIN Lost vb ON vv.Airport_id = vb.Lost_AirportID
LEFT JOIN Match kt ON vv.Airport_id = kt.Match_AirportID
WHERE vb.Matched IS NULL OR gb.Matched IS NULL
GROUP BY vv.Airport_name

我设法得到所有的计数项目的发现，丢失和匹配。

纽约已经发现了两次，输了两次，还有一场比赛。

这将正确地显示在表中。但就像我说的，如果有匹配的话，就应该从失物招领处取走。它应该是：

纽约有1人被发现，1人输，1人匹配。

我尝试了很多事情，一次我设法做到了，但后来一个机场失踪了，或者它被从被发现的地方扣除，但不是因为丢失。

我不知道解决办法是什么，有人能给我解释一下吗？

提前谢谢你，

LTKort

Answer 1

将Matched IS NULL检查放在LEFT JOIN的ON条件中，而不是WHERE。

SELECT vv.Airport_name, 
    COUNT(DISTINCT gb.Found_id) countFound, 
    COUNT(DISTINCT vb.Lost_id) countLost, 
    COUNT(DISTINCT kt.Match_id) countMatch
FROM Airports vv
LEFT JOIN Found gb ON vv.Airport_id = gb.Found_AirportID AND gb.Matched IS NULL
LEFT JOIN Lost vb ON vv.Airport_id = vb.Lost_AirportID AND vb.Matched IS NULL
LEFT JOIN Match kt ON vv.Airport_id = kt.Match_AirportID
GROUP BY vv.Airport_name

在WHERE中这样做的问题是，您只得到匹配Lost或Found项的结果。

Answer 2

或者，考虑加入派生的聚合表，以避免COUNT()评估期间的多到多连接：

SELECT a.AirportName, ftbl.countFound, lbtl.countLost, mtbl.countMatched
FROM Airports a
LEFT JOIN
   (SELECT f.Found_AirportID, COUNT(f.Found_id) AS countFound 
    FROM Found f
    WHERE f.Matched IS NULL
    GROUP BY f.Found_AirportID) As ftbl
ON a.Airport_id = ftbl.Found_AirportID

LEFT JOIN
   (SELECT l.Lost_AirportID, COUNT(l.Lost_id) AS countLost 
    FROM Lost l
    WHERE l.Matched IS NULL
    GROUP BY l.Lost_AirportID) As ltbl
ON a.Airport_id = ltbl.Lost_AirportID

LEFT JOIN
   (SELECT m.Match_AirportID, COUNT(m.Match_id) AS countMatched 
    FROM Matched m
    GROUP BY m.Match_AirportID) As mtbl
ON a.Airport_id = mtbl.Match_AirportID