Mysql组合搜索字段 - mysql combination search on field

mysql combination search on field

本文关键字：字段搜索组合 Mysql | 更新日期: 2023-09-27

表结构

类别表

id      name 
23      Rifle   
24      Bow 
26      Black Powder

旅行表

id    name
1     jignesh
2     Ram
3     Ravi

outfitters categories表

id  outfitters_id  categories_id
1    1             23
2    1             24
3    1             26
4    2             23
5    2             26
6    3             26

现在我的问题是什么时候选择(步枪和黑火药)意味着(23,26)结果应该是

1     jignesh
2     Ram

当前查询

SELECT distinct outfitters.id,
outfitters.name,
FROM outfitters 
LEFT JOIN categories as c1 ON (c1.categories_id='23') 
LEFT JOIN categories as c2 ON (c2.categories_id='26') 
WHERE outfitters.id=c1.outffiters_id 
OR 
outfitters.id=c2.outffiters_id 
AND outfitters.of_active=1

如果您需要必须属于您的2个类别的outfitters，您可以通过获得必须>或= 2的每个outfitter的不同计数以及您的where过滤器来做到这一点，参见根据提供的样本数据集的fiddle示例

SELECT 
  o.* 
FROM
  Outfitters o 
  JOIN outfitters_categories oc 
    ON (o.id = oc.outfitters_id) 
WHERE oc.categories_id IN (23, 26) 
GROUP BY o.id 
HAVING COUNT(DISTINCT oc.categories_id) >= 2

<<h2>看到演示/h2>

试试这个

SELECT distinct outfitters.id,
outfitters.name,
FROM outfitters 
LEFT JOIN categories as c1 ON (c1.categories_id='23') 
LEFT JOIN categories as c2 ON (c2.categories_id='26') 
WHERE (outfitters.id=c1.outffiters_id 
OR 
outfitters.id=c2.outffiters_id) 
AND outfitters.of_active=1
GROUP BY outfitters.name

你可以在子查询中这样做

SELECT name, id FROM(
    SELECT outfitters.name, outfitters.id, GROUP_CONCAT(outfitters_categories.categories_id) as gc
    FROM outfitters 
    JOIN outfitters_categories ON (outfitters.id = outfitters_categories.outfitters_id)
    WHERE outfitters_categories.categories_id IN (23,26) 
    GROUP BY outfitters.id ) 
tmp WHERE gc = "23,26"

然后你可以简单地把23 26替换成用户选择的