mysql 函数使用

Dear 丶 2023-01-22 15:58 119阅读 0赞

# 1、GROUP\_CONCAT #

功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

语法：group\_concat( \[distinct\] 要连接的字段 \[order by 排序字段 asc/desc \] \[separator '分隔符'\] )

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

## 1.1 建表 ##

CREATE TABLE `user`  (
      `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
      `age` int(11) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 8193 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES (13, '张三3', 13);
    INSERT INTO `user` VALUES (14, '张三5', 15);
    INSERT INTO `user` VALUES (15, '张三5', 15);
    INSERT INTO `user` VALUES (16, '张三3', 13);
    INSERT INTO `user` VALUES (17, '张三5', 15);
    INSERT INTO `user` VALUES (18, '张三5', 15);
    INSERT INTO `user` VALUES (19, '张三3', 13);
    INSERT INTO `user` VALUES (20, '张三5', 15);
    INSERT INTO `user` VALUES (21, '张三5', 15);
    INSERT INTO `user` VALUES (22, '张三3', 13);
    INSERT INTO `user` VALUES (23, '张三5', 15);
    INSERT INTO `user` VALUES (24, '张三5', 15);

## 1.2 使用 ##

### 1.2.1 默认方式 ###

SELECT
    	age,
    	GROUP_CONCAT( id ) 
    FROM
    USER 
    GROUP BY
    	age;

返回结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70][]

### 1.2.2 排序 ###

SELECT
    	age,
    	GROUP_CONCAT( id ORDER BY id DESC ) 
    FROM
    USER 
    GROUP BY
    	age;

返回结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 1][]

参考

[https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc][https_baijiahao.baidu.com_s_id_1595349117525189591_wfr_spider_for_pc]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70]: /images/20221020/ef56e1eca2224f009f20fa47d22b9bff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 1]: /images/20221020/d130ab34db664b90a0913867be073154.png
[https_baijiahao.baidu.com_s_id_1595349117525189591_wfr_spider_for_pc]: https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc