在执行其他列检查后，MySQL选择具有特定列值的一行。
在执行其他列检查后，MySQL选择具有特定列值的一行。
提问于 2022-05-19 07:47:53
我有一张包含照片元数据的表格:照片属于哪个画廊，属于哪个人(可以通过名称或每个画廊内部分配的内部标识来识别人)，照片中的人是否戴普通眼镜或墨镜，或者两者都不戴：
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 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 |
+----+------------+----------------------+------------------+----------------+------------------+----------------+现在，我的任务是获取一张照片清单，其中包含一个人穿着任何一件。眼镜或墨镜，但我只需要找到一张这样的照片，每个人在整个桌子上。这有点复杂，因为一个人可以通过所有数据库的全局id (person_name_id)或每个库中的本地id (person_id_in_gallery)来识别。所需的查询结果如下：
+----------------+
| 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)然而，我所有使用DISTINCT和GROUP BY语句从每个独特的人中选择一张照片的尝试都失败了，我将非常感谢在这个场景中正确使用它们的帮助。
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-05-19 08:12:48
您可以尝试以下操作(在[医]小提琴上进行测试)
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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72300602
复制相关文章
相似问题
腾讯云开发者
