mysql加入csv字段? [英] mysql join on csv field?
本文介绍了mysql加入csv字段?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有三个如下表
http://sqlfiddle.com/#!2/82212/6 :
CREATE TABLE IF NOT EXISTS `cat` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`color_options` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
INSERT INTO `cat` (`id`, `color_options`) VALUES (1, '1,2,3,4');
CREATE TABLE IF NOT EXISTS `template` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`cat_id` int(15) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
);
INSERT INTO `template` (`id`, `cat_id`) VALUES (1, 1);
CREATE TABLE IF NOT EXISTS `color` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`code` varchar(6) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
INSERT INTO `color` (`id`, `name`, `code`) VALUES
(1, 'Black', '000000'),
(2, 'Red', 'FF0000'),
(3, 'Blue', '0000FF'),
(4, 'Green', '00FF00');
如果我运行
SELECT *
FROM template
LEFT JOIN cat ON cat.id=template.id;
那我会得到
id cat_id color_options
1 1 1,2,3,4
如何获取文本(黑色,红色,蓝色,绿色)中的颜色选项而不是纯数字?
How do I get the color options in text(Black,Red,BLue,Green) instead of plain numbers?
我尝试使用连接,但不适用于csv字段.
I tried using join but it doesn't work on csv field.
预先感谢
推荐答案
带有GROUP_CONCAT()的子查询
A sub query with GROUP_CONCAT()
SELECT *,(SELECT GROUP_CONCAT(name) FROM color WHERE CONCAT(',',cat.color_options,',') LIKE CONCAT('%,',color.id,',%')) color_options_text
FROM template
LEFT JOIN cat ON cat.id=template.id
其他人对我来说使用了新功能,这也可以使用&读起来更干净
Someone else used a function new to me, this also works & is cleaner to read
SELECT *,(SELECT GROUP_CONCAT(name) FROM color WHERE FIND_IN_SET(color.id,cat.color_options)) color_options_text
FROM template
LEFT JOIN cat ON cat.id=template.id
这篇关于mysql加入csv字段?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文