时间戳

首页 > 解决方案 > 仅当值与多个其他值的 100% 匹配时才获取数据 SQL

mysql - 仅当值与多个其他值的 100% 匹配时才获取数据 SQL

问题描述

我有两个像这样加入的表：

SELECT * FROM color_photo c
LEFT JOIN photos p
ON c.photo_id = p.id
WHERE c.color_id IN (4,9,11)

上面给出了以下结果：

Array
(
    [id] => 4
    [photo_id] => 4
    [color_id] => 4
    [path] => photos/419d7f6ce1043f472faf44ee517c3c67.jpg
    [preview] => images/photos/previews/preview-9eba598915ea34de6d80b537a25610f3.jpg
    [thumbnail] => images/photos/thumbnails/thumb-9eba598915ea34de6d80b537a25610f3.png
    [name] => golden retriever mix dog walking in the forest.jpg
    [extension] => jpg
    [photographer_id] => 1
    [popularity] => 0
    [order] => 500
    [created_at] => 2021-07-16 11:32:55
    [updated_at] => 2021-07-16 11:32:55
)
Array
(
    [id] => 2
    [photo_id] => 2
    [color_id] => 9
    [path] => photos/604af943f1565e02de8f3d98202f6226.jpg
    [preview] => images/photos/previews/preview-5fbba3dc932f4304712dfc65860679d1.jpg
    [thumbnail] => images/photos/thumbnails/thumb-5fbba3dc932f4304712dfc65860679d1.png
    [name] => 97903847_m.jpg
    [extension] => jpg
    [photographer_id] => 1
    [popularity] => 8
    [order] => 500
    [created_at] => 2021-06-23 10:08:49
    [updated_at] => 2021-07-16 15:01:01
)
Array
(
    [id] => 3
    [photo_id] => 3
    [color_id] => 9
    [path] => photos/9bc2dc45f3473252d4353446c8dbc168.jpg
    [preview] => images/photos/previews/preview-9bc2dc45f3473252d4353446c8dbc168.jpg
    [thumbnail] => images/photos/thumbnails/thumb-9bc2dc45f3473252d4353446c8dbc168.png
    [name] => 94507149_m.jpg
    [extension] => jpg
    [photographer_id] => 1
    [popularity] => 7
    [order] => 499
    [created_at] => 2021-06-23 10:09:46
    [updated_at] => 2021-07-20 10:09:42
)
Array
(
    [id] => 1
    [photo_id] => 1
    [color_id] => 11
    [path] => photos/c3b2e455ded9f954fed6a4e039dc6cfa.png
    [preview] => images/photos/previews/preview-c3b2e455ded9f954fed6a4e039dc6cfa.jpg
    [thumbnail] => images/photos/thumbnails/thumb-c3b2e455ded9f954fed6a4e039dc6cfa.png
    [name] => a2logo_opacity_big.png
    [extension] => png
    [photographer_id] => 1
    [popularity] => 7
    [order] => 500
    [created_at] => 2021-06-23 10:08:21
    [updated_at] => 2021-07-04 18:33:14
)
Array
(
    [id] => 3
    [photo_id] => 3
    [color_id] => 11
    [path] => photos/9bc2dc45f3473252d4353446c8dbc168.jpg
    [preview] => images/photos/previews/preview-9bc2dc45f3473252d4353446c8dbc168.jpg
    [thumbnail] => images/photos/thumbnails/thumb-9bc2dc45f3473252d4353446c8dbc168.png
    [name] => 94507149_m.jpg
    [extension] => jpg
    [photographer_id] => 1
    [popularity] => 7
    [order] => 499
    [created_at] => 2021-06-23 10:09:46
    [updated_at] => 2021-07-20 10:09:42
)

我的问题的重要行是photo_id和color_id。一张照片可以有多种颜色。如您所见photo_id: 3，两者都存在 2 次不同的color_id. 这些 color_ids 从另一个页面发布到此查询，因此它们(4,9,11)是动态的并且可以是任意组合。

我希望能够按颜色过滤照片。因此，例如一张狗的照片有三种颜色，应该出现4,9,11带有 2 的狗。photo_id但是当发布狗照片没有的颜色 id 时，例如 12，所以你会得到4,9,11,12我希望从结果中删除狗。

我知道IN这不是正确的 SQL，因为它只会检索与这些 color_id 中的任何一个匹配的数据。我只想检索与所有 color_id 匹配的照片，如果有一张不匹配，请从结果中完全删除此 photo_id。我怎样才能做到这一点？我正在使用 MYSQL。

我试过这样：

SELECT * FROM color_photo c
LEFT JOIN photos p
ON c.photo_id = p.id
WHERE c.color_id = 4 AND c.color_id = 9

和

SELECT * FROM color_photo c
LEFT JOIN photos p
ON c.photo_id = p.id
WHERE c.color_id IN (4) AND c.color_id IN (9)

但两者都没有返回数据。

标签： mysqlsqldatabase

解决方案

精确匹配的简单方法使用聚合和过滤GROUP_CONCAT()：

SELECT p.*
FROM color_photo c LEFT JOIN
     photos p
     ON c.photo_id = p.id
GROUP BY p.id
HAVING GROUP_CONCAT(c.color_id ORDER BY c.color_id) = '1,4,9';

如果您想要子集匹配（因此，照片中可能有更多颜色），我建议：

SELECT p.*
FROM color_photo c LEFT JOIN
     photos p
     ON c.photo_id = p.id
WHERE c.color_id IN (1, 4, 9)
GROUP BY p.id
HAVING COUNT(*) = 3;

推荐阅读