DROP TABLE IF EXISTS test;
CREATE TABLE test ( `taochanid` varchar(100) DEFAULT NULL,
`taochanvalue` varchar(2000) DEFAULT NULL,
`num` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
INSERT INTO test VALUES ('bbaa', '页数:10', '6');
INSERT INTO test VALUES ('bbaa', '封面:玻璃面' ,'1');
INSERT INTO test VALUES ('aeww', '封面:玻璃面', '1');
INSERT INTO test VALUES ('aeww', '册芯:无缝', '0');
INSERT INTO test VALUES ('aeww', '价格:150', '7');
INSERT INTO test VALUES ('bbaa', '尺寸:8X5', '5');
INSERT INTO test VALUES ('aeww', '淋膜:超光膜', '2');
INSERT INTO test VALUES ('bbaa', '淋膜:超光膜', '2');
INSERT INTO test VALUES ('bbaa', '册芯:无缝', '0');
INSERT INTO test VALUES ('bbaa', '烫边:黑色', '4');
INSERT INTO test VALUES ('bbaa', '顺序:有序', '3');
INSERT INTO test VALUES ('aeww', '页数:10', '6');
INSERT INTO test VALUES ('bbaa', '递增页价格:12', '8');
INSERT INTO test VALUES ('aeww', '顺序:有序', '3');
INSERT INTO test VALUES ('aeww', '烫边:黑色', '4');
INSERT INTO test VALUES ('aeww', '尺寸:8X5', '5');
INSERT INTO test VALUES ('bbaa', '价格:150', '7');
INSERT INTO test VALUES ('aeww', '递增页价格:12', '8');现在查询的结果是这样:
select a.taochanid,group_concat(a.taochanvalue) as taochanvalue from test a GROUP BY a.taochanid
----------------------------------------------------------
taochanid taochanvalue
aeww 封面:玻璃面,册芯:无缝,价格:150,淋膜:超光膜,页数:10,顺序:有序,烫边:黑色,尺寸:8X5,递增页价格:12
bbaa 页数:10,封面:玻璃面,尺寸:8X5,淋膜:超光膜,册芯:无缝,烫边:黑色,顺序:有序,递增页价格:12,价格:150我想要的结果是把taochanvalue根据num列排序,现在就不知道怎么排序才能得到下面的结果:----------------------------------------------------------
taochanid taochanvalue
aeww 册芯:无缝封面:玻璃面淋膜:超光膜顺序:有序烫边:黑色尺寸:8X5页数:10价格:150递增页价格:12
bbaa 册芯:无缝封面:玻璃面淋膜:超光膜顺序:有序烫边:黑色尺寸:8X5页数:10价格:150递增页价格:12也就是把taochanvalue这一列按num排序相加
+-----------+----------------+------+
| taochanid | taochanvalue | num |
+-----------+----------------+------+
| bbaa | 页数:10 | 6 |
| bbaa | 封面:玻璃面 | 1 |
| aeww | 封面:玻璃面 | 1 |
| aeww | 册芯:无缝 | 0 |
| aeww | 价格:150 | 7 |
| bbaa | 尺寸:8X5 | 5 |
| aeww | 淋膜:超光膜 | 2 |
| bbaa | 淋膜:超光膜 | 2 |
| bbaa | 册芯:无缝 | 0 |
| bbaa | 烫边:黑色 | 4 |
| bbaa | 顺序:有序 | 3 |
| aeww | 页数:10 | 6 |
| bbaa | 递增页价格:12 | 8 |
| aeww | 顺序:有序 | 3 |
| aeww | 烫边:黑色 | 4 |
| aeww | 尺寸:8X5 | 5 |
| bbaa | 价格:150 | 7 |
| aeww | 递增页价格:12 | 8 |
+-----------+----------------+------+
18 rows in set (0.00 sec)mysql> select a.taochanid,group_concat(a.taochanvalue ORDER BY num ) as taochanvalue from test a GROUP BY a.taochanid;
+-----------+--------------------------------------------------------------------------------------------------------+
| taochanid | taochanvalue |
+-----------+--------------------------------------------------------------------------------------------------------+
| aeww | 册芯:无缝,封面:玻璃面,淋膜:超光膜,顺序:有序,烫边:黑色,尺寸:8X5,页数:10,价格:150,递增页价格:12 |
| bbaa | 册芯:无缝,封面:玻璃面,淋膜:超光膜,顺序:有序,烫边:黑色,尺寸:8X5,页数:10,价格:150,递增页价格:12 |
+-----------+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)mysql>
select taochanid,group_concat(taochanvalue ORDER BY `num` SEPARATOR ',' ) from `test` GROUP BY taochanid;
from test
GROUP BY taochanid;