问题描述
MySQL.两列,同一张表.
第1列具有product_id
第2列具有category_ids(有时为2个类别,因此看起来像23,43)
如果与product_id关联的类别ID超过1个,如何编写查询以返回带有单独行的product_id,category_ids列表.
MySQL. Two columns, same table.
Column 1 has product_id
Column 2 has category_ids (sometimes 2 categories, so will look like 23,43)
How do i write a query to return a list of product_id, category_ids, with a seperate row if there is more than 1 category_id associated with a product_id.
即
表格:
product_id | category_ids
100 | 200,300
101 | 201
查询结果:未尝试修改表
100 | 200
100 | 300
101 | 201
(注)我实际上根本不希望操纵该表.只是在PHP中进行查询,因此我可以根据需要使用数据.
(note) I don't actually wish to manipulate the table at all. Just doing a query in PHP, so i can use the data as needed.
推荐答案
您的数据库表实现似乎设计不好,但是在您的情况下,您需要的是GROUP_CONCAT的反向函数,但是不幸的是,它在MySQL中不存在.
Your database table implementation seems bad designed, however in your case what you need would be a reverse function of GROUP_CONCAT, but unfortunately it doesn't exist in MySQL.
您有两个可行的解决方案:
You have two viable solutions :
- 更改存储数据的方式(允许在
product_id
字段上重复,并为不同的category_id
放置具有相同product_id
的多条记录) - 从应用程序内部处理查询结果(您在问题中提到了PHP),在这种情况下,您必须拆分
category_ids
列值并由您自己的 组合结果集
- Change the way you store the data (allow duplicate on the
product_id
field and put multiple records with the sameproduct_id
for differentcategory_id
) - Manipulate the query result from within your application (you mentioned PHP in your question), in this case you have to split the
category_ids
column values and assemble a result set by your own
还有我发现的第三个解决方案,就像一个把戏(使用临时表和存储过程),首先,您必须声明此存储过程:
There is also a third solution that i have found that is like a trick (using a temporary table and a stored procedure), first of all you have to declare this stored procedure :
DELIMITER $$
CREATE PROCEDURE csv_Explode( sSepar VARCHAR(255), saVal TEXT )
body:
BEGIN
DROP TEMPORARY TABLE IF EXISTS csv_Explode;
CREATE TEMPORARY TABLE lib_Explode(
`pos` int unsigned NOT NULL auto_increment,
`val` VARCHAR(255) NOT NULL,
PRIMARY KEY (`pos`)
) ENGINE=Memory COMMENT='Explode() results.';
IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;
SET @saTail = saVal;
SET @iSeparLen = LENGTH( sSepar );
create_layers:
WHILE @saTail != '' DO
# Get the next value
SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
INSERT INTO lib_Explode SET val = @sHead;
END WHILE;
END; $$
DELIMITER ;
然后,您必须调用传递要爆炸的列中的数组的过程:
Then you have to call the procedure passing the array in the column you want to explode :
CALL csv_explode(',', (SELECT category_ids FROM products WHERE product_id = 100));
此后,您可以通过以下方式在临时表中显示结果:
After this you can show results in the temporary table in this way :
SELECT * FROM csv_explode;
结果集将为:
+-----+-----+
| pos | val |
+-----+-----+
| 1 | 200 |
| 2 | 300 |
+-----+-----+
这可能是您的起点...
It could be a starting point for you ...
这篇关于将结果中用逗号分隔的值从一列拆分为两行.的MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!