有表:t(id, url)两个字段,
-- 数据格式如下:(其中urls中的每部分数据以逗号隔开
id urls
1 luoyoumou1,luoyoumou2,luoyoumou3,...,luoyoumouN
2 liming1,lili-- 其url怎么转换成列的格式呢?
-- 型如:
1 luoyoumou1
1 luoyoumou2
...
1 luoyoumouN
2 liming1,
2 lili
-- 数据格式如下:(其中urls中的每部分数据以逗号隔开
id urls
1 luoyoumou1,luoyoumou2,luoyoumou3,...,luoyoumouN
2 liming1,lili-- 其url怎么转换成列的格式呢?
-- 型如:
1 luoyoumou1
1 luoyoumou2
...
1 luoyoumouN
2 liming1,
2 lili
http://hi.baidu.com/jeakccc/blog/item/c54696cede8f6c34f8dc61ab.html
然后通过 JOIN查询 和SUBSTRING_INDEX来生成结果。
-- 如果用JOIN的话,怎么操作呢?
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)root@localhost(test)10:34:32>select *from t2;
+------+---------------------------------------------+
| id | names |
+------+---------------------------------------------+
| 1 | luoyoumou1,luoyoumou2,luoyoumou3,luoyoumou4 |
| 1 | lili1,lili2,lili3,lili4 |
+------+---------------------------------------------+
2 rows in set (0.00 sec)
-- 请问:用JOIN语句的话,怎么写呢?
create table t2(id int);insert into t1(id,names) values(1,'luoyoumou1,luoyoumou2,luoyoumou3,luoyoumou4');
insert into t1(id,names) values(2,'lili1,lili2,lili3,lili');insert into t2(id) values(1);
insert into t2(id) values(2);
insert into t2(id) values(3);
insert into t2(id) values(4);
insert into t2(id) values(5);select t1.id, t2.id id2, reverse(substring_index(reverse(substring_index(concat(t1.names,','),',',t2.id)),',',1)) names
from t1, t2
where reverse(substring_index(reverse(substring_index(concat(t1.names,','),',',t2.id)),',',1))<>''
order by t1.id, t2.id;
SELECT *,SUBSTRING_INDEX(
SUBSTRING_INDEX(a1.SUBJECT,',',b.id),',',-1)
FROM a a1 LEFT JOIN zz.lsb1 b
ON (LENGTH(a1.SUBJECT)-LENGTH(REPLACE(a1.SUBJECT,',','')))+1>=b.id;
LSB1:内容 1-20000,字段ID