数据表
ID PRODUCT_ID TAG_WORDS
1 1 婴儿
2 1 侧面
3 2 婴儿
其中
ID为自增
PRODUCT_ID为产品ID
TAG_WORDS为产品的标签希望得到的结果当输入<婴儿 侧面>时,仅能过滤出PRODUCT_ID为1的产品
烦劳大家了
ID PRODUCT_ID TAG_WORDS
1 1 婴儿
2 1 侧面
3 2 婴儿
其中
ID为自增
PRODUCT_ID为产品ID
TAG_WORDS为产品的标签希望得到的结果当输入<婴儿 侧面>时,仅能过滤出PRODUCT_ID为1的产品
烦劳大家了
SELECT *
FROM `ecs_tag`
WHERE `tag_words`
IN (
'婴儿', '侧面'
)
这样的话把产品2都选出来了
是啊,因为IN (
'婴儿', '侧面'
)
条件,确实可能存在产品1的product_id
FROM `ecs_tag` a ,`ecs_tag` b
where a.PRODUCT_ID=b.PRODUCT_ID and a.id!=b.id
and a.tag_words='婴儿' and b.tag_words='侧面'
FROM `ecs_tag`
WHERE `tag_words`
IN (
'婴儿', '侧面'
) and PRODUCT_ID =1;这样查询出来的就是满足'婴儿', '侧面'而且产品=1了吧。
SELECT TOP 1 PRODUCTID,COUNT(1) AS C FROM TB WHERE TAG_WORDS LIKE '%SDFSDF%' OR LIKE '%SDFDSF%' --这里条件自己组合进去
GROUP BY PRODUCTID ORDER BY C DESC
from (
select PRODUCT_ID,group_concat(TAG_WORDS)
from tb A
group by PRODUCT_ID
)T
where T like '%婴儿%' and T like '%侧面%'
from ecs_tag a
where a.tag_words='婴儿'
and exists (select 1 from ecs_tag where PRODUCT_ID=a.PRODUCT_ID and tag_words='侧面')
and exists (select 1 from ecs_tag where PRODUCT_ID=a.PRODUCT_ID and tag_words='xxx')
and exists (select 1 from ecs_tag where PRODUCT_ID=a.PRODUCT_ID and tag_words='yyy')
+----+------------+-----------+
| id | product_id | tag_words |
+----+------------+-----------+
| 1 | 1 | 婴儿 |
| 2 | 1 | 侧面 |
| 3 | 2 | 婴儿 |
+----+------------+-----------+
3 rows in set (0.02 sec)mysql> select a.product_id from baby a,baby b
-> where a.product_id=b.product_id
-> and a.tag_words='婴儿' and b.tag_words='侧面';
+------------+
| product_id |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)