mysql - 我应该如何合并这些选择并缩小结果集？

Question

我有一个巨大的查询，可以从一系列关键字中过滤结果。

select distinct textures.id from textures
WHERE ((textures.id in (
        select tt.texture_id
        from tag_texture tt join tags t
        on t.id = tt.tag_id
        where t.name in ('tag1', 'tag2')
        group by tt.texture_id
        HAVING COUNT(DISTINCT t.id) = 2
        )
)  OR (textures.id in (
        select ct.texture_id
        from category_texture ct join categories c
        on c.id = ct.category_id
        where c.name in ('category1', 'category2')
        group by ct.texture_id
        HAVING COUNT(DISTINCT c.id) = 2
        )
)  OR (textures.id in (
        select tex.id
        from textures tex
        where tex.name LIKE 'texturename'
        group by tex.id
        HAVING COUNT(DISTINCT tex.id) = 1
        )
) ) AND textures.is_published = 1

问题是，如果我搜索texturename tag1，将找到所有texturename结果，即使它们与标签无关。但是，如果我搜索“tag1 tag2”，结果列表将被过滤掉(结果比仅搜索 tag1 少)。显然，将这​​些 OR 更改为 AND 可以进一步扩大结果范围。

合并这些结果的最佳方法是什么，以便每次过滤单词时都能缩小结果集的范围？

Answer 1

将所有 OR 更改为 AND 应该可以解决问题:

SELECT id, name 
FROM textures
WHERE ((textures.id in (
        select tt.texture_id
        from tag_texture tt join tags t
        on t.id = tt.tag_id
        where t.name in ('1k', 'test')
        group by tt.texture_id
        HAVING COUNT(DISTINCT t.id) = 2
        )
)  AND (textures.id in (
        select ct.texture_id
        from category_texture ct join categories c
        on c.id = ct.category_id
        where c.name in ('mine')
        group by ct.texture_id
        HAVING COUNT(DISTINCT c.id) = 1
        )
)  AND (textures.id in (
        select tex.id
        from textures tex
        where tex.name LIKE '%apple%'
        group by tex.id
        HAVING COUNT(DISTINCT tex.id) = 1
        )
) ) AND textures.is_published = 1

SqlFiddle

在此查询中无需使用DISTINCT。您没有与任何其他表连接，因此不会导致结果相乘。

如果您想在所有字段中搜索相同的关键字，并要求每个字段至少有一个匹配，请去掉 GROUP BY 和 HAVING 条款。

select textures.id, textures.name from textures
WHERE ((textures.id in (
        select tt.texture_id
        from tag_texture tt join tags t
        on t.id = tt.tag_id
        where t.name in ('1k', 'test', 'apple', 'mine')
        )
)  AND (textures.id in (
        select ct.texture_id
        from category_texture ct join categories c
        on c.id = ct.category_id
        where c.name in ('1k' 'test', 'apple', 'mine')
        )
)  AND (textures.id in (
        select tex.id
        from textures tex
        where tex.name LIKE '%1k%' OR tex.name LIKE '%test%' OR tex.name LIKE '%apple%'
          OR tex.name LIKE '%mine%'
        )
) ) AND textures.is_published = 1

我将我的添加到关键字列表中，因为否则categories表中没有匹配项。

SqlFiddle