MySQL 字符串连接函数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50718
 Source Host           : 127.0.0.1
 Source Database       : test

 Target Server Type    : MySQL
 Target Server Version : 50718
 File Encoding         : utf-8

 Date: 07/14/2017 23:53:40 PM
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `demo`
-- ----------------------------
DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
  `key` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
  `val` varchar(50) NOT NULL DEFAULT '' COMMENT '用户姓',
  `createdAt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳',
  `updatedAt` int(10) unsigned DEFAULT '0' COMMENT '更新时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `demo`
-- ----------------------------
BEGIN;
INSERT INTO `demo` VALUES ('1', 'A', '10', '0', '0'), ('2', 'B', '20', '0', '0'), ('3', 'B', '21', '0', '0'), ('4', 'C', '30', '0', '0'), ('5', 'C', '31', '0', '0'), ('6', 'C', '32', '0', '0'), ('7', 'D', '40', '0', '0'), ('8', 'D', '41', '0', '0'), ('9', 'D', '42', '0', '0'), ('10', 'D', '43', '0', '0'), ('11', 'E', '50', '0', '0'), ('12', 'E', '51', '0', '0'), ('13', 'E', '52', '0', '0'), ('14', 'E', '53', '0', '0'), ('15', 'E', '54', '0', '0'), ('16', 'F', '60', '0', '0'), ('17', 'F', '61', '0', '0'), ('18', 'F', '62', '0', '0'), ('19', 'F', '63', '0', '0'), ('20', 'F', '64', '0', '0'), ('21', 'F', '65', '0', '0');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT GROUP_CONCAT(
           CONCAT_WS(': ', `key`, `val`)
           ORDER BY
           `id` DESC SEPARATOR ', '
       ) AS info
FROM
  demo
GROUP BY
  `key`;

// 结果
A: 10
B: 21, B: 20
C: 32, C: 31, C: 30
D: 43, D: 42, D: 41, D: 40
E: 54, E: 53, E: 52, E: 51, E: 50
F: 65, F: 64, F: 63, F: 62, F: 61, F: 60

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT GROUP_CONCAT(DISTINCT `val`) AS `vals`
FROM
  demo
GROUP BY
  `key`;

// 结果
10
20,21
30,31,32
40,41,42,43
50,51,52,53,54
60,61,62,63,64,65

1
2
3
4

SELECT CONCAT('a', 'b', 'c', NULL) AS string;

// 结果
NULL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT CONCAT_WS(
           '',
           'a',
           'b',
           'c',
           '',
           NULL,
           FALSE,
           TRUE
       ) AS string;

// 结果
abc01

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

SELECT CONCAT_WS('|', `key`, `val`) AS info
FROM
  demo;

// 结果
A|10
B|20
B|21
C|30
C|31
C|32
D|40
D|41
D|42
D|43
E|50
E|51
E|52
E|53
E|54
F|60
F|61
F|62
F|63
F|64
F|65

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT GROUP_CONCAT(`key` SEPARATOR ',') AS `val`
FROM
  (
    SELECT CONCAT(
               `key`,
               ': ',
               GROUP_CONCAT(`val` SEPARATOR ', ')
           ) AS `key`
    FROM
      demo
    GROUP BY
      `key`
  ) custom
GROUP BY
  `key`;

// 结果
A: 10
B: 20, 21
C: 30, 31, 32
D: 40, 41, 42, 43
E: 50, 51, 52, 53, 54
F: 60, 61, 62, 63, 64, 65