执行其他列检查后，MySQL选择具有特定列值的单行

Question

我有一张包含照片元数据的桌子：哪个画廊照片属于哪个人（可以用名称来识别人，也可以在每个画廊内部分配的内部ID），无论照片中的人是否戴着常规眼镜或深色眼镜，或者都不是：

+----+------------+----------------------+------------------+----------------+------------------+----------------+
| id | gallery_id | person_id_in_gallery |  person_name_id  |  wear_glasses  |  wear_sunglasses | image_filename |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  1 |     1      |          1           |       NULL       |       1        |         0        |       xa.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  2 |     1      |          1           |       NULL       |       1        |         0        |       xb.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  3 |     1      |          3           |       NULL       |       1        |         0        |       xc.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  4 |     2      |          1           |       NULL       |       0        |         1        |       ya.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  5 |     2      |          2           |       NULL       |       0        |         1        |       yb.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  6 |     2      |          2           |       NULL       |       0        |         1        |       yc.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  7 |     2      |          3           |       NULL       |       0        |         1        |       yd.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  8 |     3      |         NULL         |         1        |       1        |         0        |       za.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
|  9 |     3      |         NULL         |         1        |       1        |         0        |       zb.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 10 |     3      |         NULL         |         2        |       0        |         1        |       zc.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 11 |     3      |         NULL         |         2        |       0        |         1        |       zd.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 12 |     3      |         NULL         |         3        |       0        |         0        |       ze.jpg   |
+----+------------+----------------------+------------------+----------------+------------------+----------------+

现在，我的任务是获取包含一个穿着任何一个reg的人的照片列表。眼镜或深色眼镜，但我只需要找到整个桌子上每个人的照片。它会变得有些复杂，因为在所有数据库（Person_Name_ID）或每个画廊中的本地ID（Person_ID_IN_Gallery）中，可以通过全局ID识别一个人。所需的查询结果如下：

+----------------+
| image_filename |
+----------------+
|       xa.jpg   |
+----------------+
|       xc.jpg   |
+----------------+
|       ya.jpg   |
+----------------+
|       yb.jpg   |
+----------------+
|       yd.jpg   |
+----------------+
|       za.jpg   |
+----------------+
|       zc.jpg   |
+----------------+

这是我用来获取所有带有眼镜和太阳镜的照片的查询。

SELECT image_filename FROM photos_table WHERE (wear_glasses = 1 or wear_sunglasses = 1) and (person_id_in_gallery IS NOT NULL or person_name_id IS NOT NULL)

但是，我所有尝试使用不同的陈述使用不同的尝试来选择每个独特人的照片失败的尝试，我非常感谢在这种情况下正确使用它们的帮助。

Answer 1

您可以尝试以下（在

SELECT MIN(image_filename) AS image_filename
FROM photos_table
WHERE (wear_glasses = 1 or wear_sunglasses = 1) 
AND (person_id_in_gallery IS NOT NULL or person_name_id IS NOT NULL)
GROUP BY gallery_id, person_id_in_gallery, person_name_id;