Mysql常用函数

新建表

CREATE TABLE `stu_class` (
  `id` varchar(10) NOT NULL COMMENT '班级名称',
  `name` varchar(255) DEFAULT NULL COMMENT '班级名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `u96e8`.`stu_class`(`id`, `name`) VALUES ('1001', '男神一班');
INSERT INTO `u96e8`.`stu_class`(`id`, `name`) VALUES ('1002', '女神二班');
INSERT INTO `u96e8`.`stu_class`(`id`, `name`) VALUES ('1003', '女神三班');
CREATE TABLE `student` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stu_name` varchar(255) DEFAULT NULL COMMENT '学生姓名',
  `stu_gender` varchar(255) DEFAULT NULL COMMENT '学生性别',
  `stu_birthday` date DEFAULT NULL COMMENT '学生生日',
  `stu_age` int(11) DEFAULT NULL COMMENT '学生年龄',
  `stu_class_id` int(11) DEFAULT NULL COMMENT '学生班级',
  PRIMARY KEY (`stu_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
INSERT INTO `u96e8`.`student`(`stu_id`, `stu_name`, `stu_gender`, `stu_birthday`, `stu_age`, `stu_class_id`) VALUES (1, '胡歌', '男', '1982-09-20', 49, 1001);
INSERT INTO `u96e8`.`student`(`stu_id`, `stu_name`, `stu_gender`, `stu_birthday`, `stu_age`, `stu_class_id`) VALUES (2, '周星星', '男', '1962-06-22', 59, 1001);
INSERT INTO `u96e8`.`student`(`stu_id`, `stu_name`, `stu_gender`, `stu_birthday`, `stu_age`, `stu_class_id`) VALUES (3, 'IU', '女', '1993-05-16', 28, 1002);
INSERT INTO `u96e8`.`student`(`stu_id`, `stu_name`, `stu_gender`, `stu_birthday`, `stu_age`, `stu_class_id`) VALUES (4, '周慧敏', '女', '1967-11-10', 54, 1002);
INSERT INTO `u96e8`.`student`(`stu_id`, `stu_name`, `stu_gender`, `stu_birthday`, `stu_age`, `stu_class_id`) VALUES (5, '张敏\r\n', '女', '1968-02-07', 52, 1003);
INSERT INTO `u96e8`.`student`(`stu_id`, `stu_name`, `stu_gender`, `stu_birthday`, `stu_age`, `stu_class_id`) VALUES (6, '范浩杰', '男', '2000-07-21', 22, 1003);

常用函数

GROUP_CONCAT(c); 拼接分组后被叠加部分的数据

select s.stu_id, GROUP_CONCAT(s.stu_name), s.stu_gender, s.stu_birthday, s.stu_age, s.stu_class_id, c.name from student s LEFT JOIN stu_class c on s.stu_class_id = c.id GROUP BY c.name

查询结果：

5 张敏,范浩杰 女 1968-02-07 52 1003 女神三班
3 IU,周慧敏 女 1993-05-16 28 1002 女神二班
1 胡歌,周星星 男 1982-09-20 49 1001 男神一班

SUBSTRING_INDEX(str, delim, count) ;

str:要处理的字符串 delim:分隔符 count:计数 可将分组叠加的数据按照可排序字段排序后取第一个索引的值

按照班级分组后，姓名分组集按照id倒序后取第一个人的名称

select s.stu_id,SUBSTRING_INDEX(GROUP_CONCAT(s.stu_name ORDER BY s.stu_id DESC), ',', 1), s.stu_gender, s.stu_birthday, s.stu_age, s.stu_class_id, c.name from student s LEFT JOIN stu_class c on s.stu_class_id = c.id GROUP BY c.name

查询结果：

5 范浩杰 女 1968-02-07 52 1003 女神三班
3 周慧敏 女 1993-05-16 28 1002 女神二班
1 周星星 男 1982-09-20 49 1001 男神一班

按照班级分组后，姓名分组集按照id升序后取第一个人的名称

select s.stu_id,SUBSTRING_INDEX(GROUP_CONCAT(s.stu_name ORDER BY s.stu_id asc), ',', 1), s.stu_gender, s.stu_birthday, s.stu_age, s.stu_class_id, c.name from student s LEFT JOIN stu_class c on s.stu_class_id = c.id GROUP BY c.name

查询结果：

5 张敏 女 1968-02-07 52 1003 女神三班
3 IU 女 1993-05-16 28 1002 女神二班
1 胡歌 男 1982-09-20 49 1001 男神一班

此时性别、生日等信息如果要跟人名匹配上也需要做和名字一样的处理即可

应用：记录表中的冗余字段需要显示最新的冗余信息，旧的冗余信息并不被维护

\<![CDATA[>=]]> 特殊符号处理

个性化统计

SELECT space_id,COUNT(receivable_money),SUM(case when received_money = receivable_money then 1 else 0 end) receivedNum,SUM(case when final_money != 0 then 1 else 0 end) finalNum,CONCAT(ROUND((1-(SUM(case when final_money != 0 then 1 else 0 end)/COUNT(receivable_money)))*100,2),'%') as proportion from bill_fee_details WHERE is_deleted = 0
and status = 9
and charge_date_begin >= '' and charge_date_end <= ''
GROUP BY space_id