mysql 拼装字段

数据库

/*
Navicat MySQL Data Transfer

Source Server         : 160
Source Server Version : 50629
Source Host           : 192.168.1.160:3306
Source Database       : data_report

Target Server Type    : MYSQL
Target Server Version : 50629
File Encoding         : 65001

Date: 2018-04-24 19:44:03
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `t_datareport_im_msg`
-- ----------------------------
DROP TABLE IF EXISTS `t_datareport_im_msg`;
CREATE TABLE `t_datareport_im_msg` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '白名单用户ID',
  `reportDate` varchar(11) NOT NULL COMMENT '报表日期',
  `createTime` datetime DEFAULT NULL,
  `userCount` bigint(20) DEFAULT NULL COMMENT '进入审核人数',
  `msgCount` bigint(20) DEFAULT NULL COMMENT '消息条数',
  `textMsgCount` bigint(20) DEFAULT NULL COMMENT '文本消息条数',
  `imgMsgCount` bigint(20) DEFAULT NULL COMMENT '图片消息条数',
  `voiceMsgCount` bigint(20) DEFAULT NULL COMMENT '语音消息条数',
  `frozenUserCount` bigint(20) DEFAULT NULL COMMENT '冻结用户数',
  `alarmUserCount` bigint(20) DEFAULT NULL COMMENT '警告用户数',
  `bannedTalkUserCount` bigint(20) DEFAULT NULL COMMENT '禁言用户数',
  `frozenMsgCount` bigint(20) DEFAULT NULL COMMENT '冻结消息数',
  `textFrozenMsgCount` bigint(20) DEFAULT NULL COMMENT '冻结文本消息数',
  `imgFrozenMsgCount` bigint(20) DEFAULT NULL COMMENT '冻结图片消息数',
  `voiceFrozenMsgCount` bigint(20) DEFAULT NULL COMMENT '冻结语音消息数',
  `alarmMsgCount` bigint(20) DEFAULT NULL COMMENT '警告消息数',
  `textAlarmMsgCount` bigint(20) DEFAULT NULL COMMENT '警告文本消息数',
  `imgAlarmMsgCount` bigint(20) DEFAULT NULL COMMENT '警告图片消息数',
  `voiceAlarmMsgCount` bigint(20) DEFAULT NULL COMMENT '警告语音消息数',
  `bannedTalkMsgCount` bigint(20) DEFAULT NULL COMMENT '禁言消息数',
  `textBannedTalkMsgCount` bigint(20) DEFAULT NULL COMMENT '禁言文本消息数',
  `imgBannedTalkMsgCount` bigint(20) DEFAULT NULL COMMENT '禁言图片消息数',
  `voiceBannedTalkMsgCount` bigint(20) DEFAULT NULL COMMENT '禁言语音消息数',
  `bannedPlayUserCount` bigint(20) DEFAULT NULL COMMENT '禁玩用户数',
  `bannedPlayMsgCount` bigint(20) DEFAULT NULL COMMENT '禁玩消息数',
  `textBannedPlayMsgCount` bigint(20) DEFAULT NULL COMMENT '禁玩文本消息数',
  `imgBannedPlayMsgCount` bigint(20) DEFAULT NULL COMMENT '禁玩图片消息数',
  `voiceBannedPlayMsgCount` bigint(20) DEFAULT NULL COMMENT '禁玩语音消息数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_reportdate` (`reportDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19407473 DEFAULT CHARSET=utf8mb4 COMMENT='可疑消息白名单用户表';

-- ----------------------------
-- Records of t_datareport_im_msg
-- ----------------------------
INSERT INTO `t_datareport_im_msg` VALUES ('2', '2017-07-15', null, '120', '1120', '1000', '100', '20', '50', '10', '5', null, '100', '50', '10', '10', '10', '10', '5', '5', '5', '2', '2', '0', '12', '3', '4', '5');
INSERT INTO `t_datareport_im_msg` VALUES ('3', '2017-07-14', null, '120', '1120', '1000', '100', '20', '50', '10', '5', null, '100', '50', '10', '10', '10', '10', '5', '5', '5', '2', '2', '0', '12', '3', '4', '5');
INSERT INTO `t_datareport_im_msg` VALUES ('4', '2017-07-13', null, '120', '1120', '1000', '100', '20', '50', '10', '5', null, '100', '50', '10', '10', '10', '10', '5', '5', '5', '2', '2', '0', '12', '3', '4', '5');
INSERT INTO `t_datareport_im_msg` VALUES ('19407468', '2017-07-16', '2017-07-17 16:56:09', '2', '41', '16', '16', '9', '2', '1', '1', '17', '8', '8', '1', '6', '5', '0', '1', '10', '3', '0', '7', '1', '12', '3', '4', '5');
INSERT INTO `t_datareport_im_msg` VALUES ('19407469', '2017-07-17', '2017-07-20 17:29:40', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '12', '3', '4', '5');
INSERT INTO `t_datareport_im_msg` VALUES ('19407470', '2017-07-20', '2017-07-20 17:30:47', '9', '64', '61', '2', '1', '4', '3', '2', '14', '13', '0', '1', '4', '4', '0', '0', '3', '1', '2', '0', '1', '12', '3', '4', '5');
INSERT INTO `t_datareport_im_msg` VALUES ('19407471', '2017-08-18', '2017-08-19 17:36:20', '56', '149', '59', '66', '24', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '12', '3', '4', '5');
INSERT INTO `t_datareport_im_msg` VALUES ('19407472', '2017-11-17', '2017-11-18 11:23:36', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '1', '12', '3', '4', '5');

查询语句

SELECT
        reportDate,
        userCount,
        CONCAT(msgCount,'(',textMsgCount,',',imgMsgCount,',',voiceMsgCount,')') as msg,
        frozenUserCount,
        alarmUserCount,
        bannedTalkUserCount,
        bannedPlayUserCount,
        CONCAT(frozenMsgCount,'(',textFrozenMsgCount,',',imgFrozenMsgCount,',',voiceFrozenMsgCount,')') as frozen,
        CONCAT(alarmMsgCount,'(',textAlarmMsgCount,',',imgAlarmMsgCount,',',voiceAlarmMsgCount,')') as alarm,
        CONCAT(bannedTalkMsgCount,'(',textBannedTalkMsgCount,',',imgBannedTalkMsgCount,',',voiceBannedTalkMsgCount,')') as bannedtalk,
        CONCAT(bannedPlayMsgCount,'(',textBannedPlayMsgCount,',',imgBannedPlayMsgCount,',',voiceBannedPlayMsgCount,')') as bannedpaly
from t_datareport_im_msg 
order by reportDate desc

结果