mysql> select * from t_dawenwen1128;
+-------+--------+
| c_id | c_name |
+-------+--------+
| 10001 | 沙 |
| 10001 | 石 |
| 10001 | 煤 |
| 10002 | 沙 |
| 10002 | 水 |
| 10002 | 盐 |
+-------+--------+
6 rows in set (0.00 sec)mysql> select c_id,
-> SUBSTRING_INDEX(k,',',1) as name1,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(k,',',2),',',-1) as name2,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(k,',',3),',',-1) as name3
-> from (
-> select c_id,group_concat(c_name) as k /////c_name是double类型为什么会出错???
-> from t_dawenwen1128
-> group by c_id
-> ) t ;
+-------+-------+-------+-------+
| c_id | name1 | name2 | name3 |
+-------+-------+-------+-------+
| 10001 | 沙 | 石 | 煤 |
| 10002 | 沙 | 水 | 盐 |
+-------+-------+-------+-------+
2 rows in set (0.08 sec)mysql>c_name /////c_name是double类型为什么会出错???
+-------+--------+
| c_id | c_name |
+-------+--------+
| 10001 | 沙 |
| 10001 | 石 |
| 10001 | 煤 |
| 10002 | 沙 |
| 10002 | 水 |
| 10002 | 盐 |
+-------+--------+
6 rows in set (0.00 sec)mysql> select c_id,
-> SUBSTRING_INDEX(k,',',1) as name1,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(k,',',2),',',-1) as name2,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(k,',',3),',',-1) as name3
-> from (
-> select c_id,group_concat(c_name) as k /////c_name是double类型为什么会出错???
-> from t_dawenwen1128
-> group by c_id
-> ) t ;
+-------+-------+-------+-------+
| c_id | name1 | name2 | name3 |
+-------+-------+-------+-------+
| 10001 | 沙 | 石 | 煤 |
| 10002 | 沙 | 水 | 盐 |
+-------+-------+-------+-------+
2 rows in set (0.08 sec)mysql>c_name /////c_name是double类型为什么会出错???
select c_id,group_concat(cast(c_name as char)) as k
你出错的记录内容是什么,用其它类型,比如DECIMAL类型有无问题。
select c_id,group_concat(cast(c_name as char)) as k
+-------+--------+---------+
| c_id | c_name | c_namef |
+-------+--------+---------+
| 10001 | 沙 | 10.12 |
| 10001 | 石 | 10.12 |
| 10001 | 煤 | 10.12 |
| 10002 | 沙 | 10.12 |
| 10002 | 水 | 10.12 |
| 10002 | 盐 | 10.12 |
+-------+--------+---------+
6 rows in set (0.06 sec)mysql> select c_id,
-> SUBSTRING_INDEX(k,',',1) as name1,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(k,',',2),',',-1) as name2,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(k,',',3),',',-1) as name3
-> from (
-> select c_id,group_concat(c_namef) as k
-> from t_dawenwen1128
-> group by c_id
-> ) t ;
+-------+-------+-------+-------+
| c_id | name1 | name2 | name3 |
+-------+-------+-------+-------+
| 10001 | 10.12 | 10.12 | 10.12 |
| 10002 | 10.12 | 10.12 | 10.12 |
+-------+-------+-------+-------+
2 rows in set (0.00 sec)mysql>
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。