PHP和MySQL GROUP_CONCAT,带逗号分隔符,并用逗号搜索其中的位置 [英] Php and MySQL GROUP_CONCAT with separator comma and search where concat by comma
问题描述
是否可以在Group_concat中用逗号分隔来选择 并用逗号搜索其中的位置 这是我的mysql示例:
Is it that possible to select in Group_concat with separator comma and search where concat by comma Here My sample of mysql :
products_attributes_id |products_id| options_id| options_values_id
39 | 31 | 3 | 3
35 | 30 | 2 | 2
38 | 30 | 1 | 1
40 | 31 | 2 | 2
41 | 30 | 1 | 4
42 | 30 | 1 | 5
43 | 31 | 1 | 4
我要GROUP_CONCAT options_values_id
I want to GROUP_CONCAT options_values_id
SELECT *,GROUP_CONCAT(options_values_id SEPARATOR',')从products_attributes
GROUP by products_id
SELECT * , GROUP_CONCAT(options_values_id SEPARATOR ',') FROM products_attributes
GROUP by products_id
products_attributes_id | products_id | options_id | options_values_id | options_values_id
35 | 30 | 2 | 2 | 2,1,4,5
39 | 31 | 3 | 3 | 3,2,4
现在,我的问题是如何通过搜索(用逗号分隔的option_values_id)来查找product_id,如果我的值是1,2或2,4,那么我将只获得product_id = 30. 但是,如果我得到价值1,3,我将一无所获.因为product_id 30没有option_values_id 3 和product_id 31没有option_values_id 1.
Now my problem is How I will find the product_id by searching ( option_values_id with comma) if my value is 1,2 or 2,4 then I will get only product_id=30. But if I got value 1,3 I will get nothing. Because product_id 30 don't have option_values_id 3 and product_id 31 don't have option_values_id 1.
我尝试使用此代码,但无法获得product_id
I try this code but I can't get the product_id
SELECT * , GROUP_CONCAT( options_values_id
SEPARATOR ',' )
FROM `products_attributes` WHERE CONCAT(',',options_values_id,',') LIKE '%,1,2,%'
GROUP BY products_id
推荐答案
尝试一下:
SELECT
products_id ,
GROUP_CONCAT(options_values_id SEPARATOR ',') as ops
FROM
products_attributes
GROUP by
products_id
HAVING
FIND_IN_SET('1',ops)
OR FIND_IN_SET('3',ops)
这篇关于PHP和MySQL GROUP_CONCAT,带逗号分隔符,并用逗号搜索其中的位置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!