关于Mysql group_concat的应用(把相同ID的VAL用字符'/'连接起来)

原创
2015/06/14 16:26
阅读数 523

关于Mysql group_concat的应用

■事先准备:

CREATE TABLE TBL_GRP_CAT (

ID INT,

VAL CHAR

);


INSERT INTO TBL_GRP_CAT VALUES(1, 'a');

INSERT INTO TBL_GRP_CAT VALUES(1, 'b');

INSERT INTO TBL_GRP_CAT VALUES(1, 'c');

INSERT INTO TBL_GRP_CAT VALUES(2, 'd');

INSERT INTO TBL_GRP_CAT VALUES(2, 'e');

INSERT INTO TBL_GRP_CAT VALUES(2, 'd');

INSERT INTO TBL_GRP_CAT VALUES(2, 'f');


mysql> select * from TBL_GRP_CAT;

+------+------+

| ID   | VAL  |

+------+------+

|    1 | a    |

|    1 | b    |

|    1 | c    |

|    2 | d    |

|    2 | e    |

|    2 | d    |

|    2 | f    |

+------+------+

7 rows in set (0.00 sec)


mysql>


■语法:

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])


■示例

示例1:把相同ID的VAL用逗号连接起来(可以重复)

SELECT ID, group_concat(VAL) VAL FROM TBL_GRP_CAT GROUP BY ID ORDER BY ID;

mysql> SELECT ID, group_concat(VAL) VAL FROM TBL_GRP_CAT GROUP BY ID ORDER BY ID

;

+------+---------+

| ID   | VAL     |

+------+---------+

|    1 | a,b,c   |

|    2 | d,e,d,f |

+------+---------+

2 rows in set (0.00 sec)


mysql>


示例2:把相同ID的VAL用逗号连接起来(不含重复)

SELECT ID, group_concat(DISTINCT VAL) VAL FROM TBL_GRP_CAT GROUP BY ID ORDER BY ID;

mysql> SELECT ID, group_concat(DISTINCT VAL) VAL FROM TBL_GRP_CAT GROUP BY ID OR

DER BY ID;

+------+-------+

| ID   | VAL   |

+------+-------+

|    1 | a,b,c |

|    2 | d,e,f |

+------+-------+

2 rows in set (0.00 sec)


mysql>


示例3:把相同ID的VAL由大到小用逗号连接起来(可以重复)

SELECT ID, group_concat(VAL Order BY VAL DESC) VAL FROM TBL_GRP_CAT GROUP BY ID ORDER BY ID;

mysql> SELECT ID, group_concat(VAL Order BY VAL DESC) VAL FROM TBL_GRP_CAT GROU

 BY ID ORDER BY ID;

+------+---------+

| ID   | VAL     |

+------+---------+

|    1 | c,b,a   |

|    2 | f,e,d,d |

+------+---------+

2 rows in set (0.00 sec)


mysql>


示例4:把相同ID的VAL用字符'/'连接起来(可以重复)

SELECT ID, group_concat(VAL Separator '/') VAL FROM TBL_GRP_CAT GROUP BY ID ORDER BY ID;

mysql> SELECT ID, group_concat(VAL Separator '/') VAL FROM TBL_GRP_CAT GROUP BY

ID ORDER BY ID;

+------+---------+

| ID   | VAL     |

+------+---------+

|    1 | a/b/c   |

|    2 | d/e/d/f |

+------+---------+

2 rows in set (0.00 sec)


mysql>


示例5:权使用

SELECT ID, group_concat(DISTINCT VAL Order BY VAL DESC Separator '/') VAL FROM TBL_GRP_CAT GROUP BY ID ORDER BY ID;

mysql> SELECT ID, group_concat(DISTINCT VAL Order BY VAL DESC Separator '/') VAL

 FROM TBL_GRP_CAT GROUP BY ID ORDER BY ID;

+------+-------+

| ID   | VAL   |

+------+-------+

|    1 | c/b/a |

|    2 | f/e/d |

+------+-------+

2 rows in set (0.00 sec)


mysql>


在Oracle(Listagg),Sqlite(group_concat)等均有类似功能。


展开阅读全文
打赏
0
4 收藏
分享
加载中
更多评论
打赏
0 评论
4 收藏
0
分享
返回顶部
顶部