mysql - 使用多个别名在同一个表和列上进行多个连接

Question

我有一个名人照片网站。它有一个表，其中包含每张照片的所有可搜索数据，称为 photoSearch 并且完整的数据集分布在许多表中(规范化)，名为 photos，photoPeople 和人。

photoPeople 表包含 photoID 和 peopleID，people 表包含 peopleID 和人名。 photos 表包含标题、标题、高度、宽度、文件大小、销售代码等。

这是我当前的脚本，它产生了很好的结果。

SELECT 
     p.photoID, 
     p.setID, 
     p.headline, 
     p.caption,
     GROUP_CONCAT(pe.people ORDER BY pe.people ASC SEPARATOR ';') AS people
FROM 
     photos p
INNER JOIN
     (
        SELECT photoID 
        FROM photoSearch 
        WHERE MATCH (allKeywords, shortCaption)
        AGAINST ('+red +dress +claridges +hotel' IN BOOLEAN MODE) 
        LIMIT 50
     ) pids ON p.photoID = pids.photoID
***********
LEFT JOIN
     photoPeople pp ON p.photoID = pp.photoID
LEFT JOIN
     people pe ON pp.peopleID = pe.peopleID
***********
GROUP BY p.photoID

问题是，我现在不只是选择要在结果中显示的名人姓名，而是想搜索一个或多个名人姓名来更改结果。

我很确定不可能在同一列上添加两个或更多 where 子句(其中 pe.people = 'angelina jolie' 和 pe.people = 'brad pitt')，所以我很确定需要使用 JOINS 完成。

这让我有点困惑。所以我在这里问了一个关于 SO 的问题，以了解如何实现这一目标，有人建议这样做:

"Join to the 'photoPeople' and 'people' tables n times, where n is the number of people you are searching for."

我尝试了许多不同的方法来实现这一点，但到目前为止都失败了。我知道他说的是删除两行星号之间的联接(上面的代码)并将它们替换为人员表的 n 个联接，但我只是想不通。

我试过了，但是 phpMyAdmin 现在已经思考了最后 10 分钟!:

********
JOIN ( photoPeople AS pp1 JOIN people AS p1 ON pp1.peopleID = p1.peopleID) 
ON p1.people = 'Brad Pitt'
JOIN ( photoPeople AS pp2 JOIN people AS p2 ON pp2.peopleID = p2.peopleID) 
ON p2.people = 'Angelina Jolie'
********

除了上面的 FOREVER 尝试之外，我还努力了解如何更改 GROUP_CONCAT 函数以从多个连接/别名(p1、p2、p3 等)中获取结果!

拜托，我希望得到一些建议来让它正常工作。

---

测试场景

如果我按原样运行我的查询，这就是我会看到的:

Photo:    120030
Set:      8803
Headline: Brad and Angelina arrive at film premiere.
People:   Brad Pitt; Angelina Jolie

Photo:    120031
Set:      8803
Headline: Brad and Angelina arrive at film premiere.
People:   Angelina Jolie

Photo:    120032
Set:      8803
Headline: Brad and Angelina arrive at film premiere.
People:   Brad Pitt

我希望我的新查询搜索姓名，所以如果我搜索 Angeline Jolie 和 Brad Pitt，我只会看到这个:

Photo:    120030
Set:      8803
Headline: Brad and Angelina arrive at film premiere.
People:   Brad Pitt; Angelina Jolie

---

新尝试:

没有错误，但返回了 0 个结果，而“法国”中的“布拉德皮特”和“安吉丽娜朱莉”应该有 1000 个结果。

SELECT 
     p.photoID, 
     p.setID, 
     p.headline, 
     p.caption,
     GROUP_CONCAT(pe.people ORDER BY pe.people ASC SEPARATOR ';') AS people
FROM 
     photos p
INNER JOIN
     (
        SELECT photoID 
        FROM photoSearch 
        WHERE MATCH (allKeywords, shortCaption)
        AGAINST ('+France' IN BOOLEAN MODE) 
        LIMIT 50
     ) pids ON p.photoID = pids.photoID
LEFT JOIN
     photoPeople pp ON p.photoID = pp.photoID
JOIN
     people pe ON pp.peopleID = pe.peopleID
     AND (pe.people = 'Angelina Jolie' OR pe.people = 'Brad Pitt')
GROUP BY p.photoID

如果我删除 INNER JOIN 部分并运行它，它会返回 Brad 和 Angelina 的所有照片，无论他们是否在一起，这是不正确的 - 我只需要他们在一起。如果我还删除 OR pe.people = 'Brad Pitt'，它会正确返回 Angelina 的照片，我就是无法将整个事情联系起来!

Answer 1

要查找显示列表中所有人的照片，请使用

SELECT 
     p.photoID, 
     p.setID, 
     p.headline, 
     p.caption,
     GROUP_CONCAT(pe.people ORDER BY pe.people ASC SEPARATOR ';') AS people
FROM 
     photos p
INNER JOIN
     (
        SELECT photoID 
        FROM photoSearch 
        WHERE MATCH (allKeywords, shortCaption)
        AGAINST ('+red +dress +claridges +hotel' IN BOOLEAN MODE) 
        LIMIT 50
     ) pids 
   ON p.photoID = pids.photoID

INNER JOIN
     photoPeople pp ON p.photoID = pp.photoID
INNER JOIN
     people pe ON pp.peopleID = pe.peopleID
-- List of people
WHERE pe.People in ('Angelina Jolie', 'Brad Pit')
GROUP BY p.photoID
-- "2" must match number o people in the list
-- If you add third name, change this to 3
HAVING count(distinct pe.People) = 2

我已将左连接更改为内部连接，因为 where 会这样对待它们(因为 null 不与任何东西进行比较)，并且因为我觉得如果接受不匹配的照片，这个问题就没有意义了。

附言您的尝试没有成功，因为它确实将所有 Angelinas 连接到所有照片，将所有 Brads 连接到所有照片，产生了大量记录。您可能在 PhotoID 上连接了两个派生表以产生与上述查询相同的结果，但它需要另一个派生表来添加第三个名字。

SELECT photos1.PhotoID
FROM
(
  SELECT pp1.PhotoID
    FROM photoPeople AS pp1 
    JOIN people AS p1 ON pp1.peopleID = p1.peopleID
   WHERE p1.people = 'Brad Pitt'
) photos1
JOIN 
(
  SELECT pp2.PhotoID
    FROM photoPeople AS pp2
    JOIN people AS p2 ON pp2.peopleID = p2.peopleID
   WHERE p2.people = 'Angelina Jolie'
) photos2
  ON photos1.PhotoID = photos2.PhotoID

更新:组合条件以避免可能的限制 50 不匹配:

SELECT 
     p.photoID, 
     p.setID, 
     p.headline, 
     p.caption,
     GROUP_CONCAT(pe.people ORDER BY pe.people ASC SEPARATOR ';') AS people
FROM 
     photos p
INNER JOIN
     (
        SELECT distinct ps.photoID 
        FROM photoSearch ps
        INNER JOIN
             photoPeople pp ON ps.photoID = pp.photoID
        INNER JOIN
             people pe ON pp.peopleID = pe.peopleID
     -- List of people
        WHERE pe.People in ('Angelina Jolie', 'Brad Pit')
          AND MATCH (allKeywords, shortCaption)
              AGAINST ('+red +dress +claridges +hotel' IN BOOLEAN MODE) 
        GROUP BY ps.photoID
     -- "2" must match number o people in the list
     -- If you add third name, change this to 3
        HAVING count(distinct pe.People) = 2
        LIMIT 50
     ) pids 
   ON p.photoID = pids.photoID
INNER JOIN
     photoPeople pp ON p.photoID = pp.photoID
INNER JOIN
     people pe ON pp.peopleID = pe.peopleID
GROUP BY p.photoID

重新编辑:注意到我忘记在主查询中留下与人相关的连接。