首页 > 代码库 > MySql中group_concat的使用

MySql中group_concat的使用

CREATE TABLE `staff` (`id` INT(11) NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) NULL DEFAULT NULL COMMENT ‘姓名‘,`salary` BIGINT(20) NULL DEFAULT NULL COMMENT ‘薪水‘,`depart` VARCHAR(50) NULL DEFAULT NULL COMMENT ‘部门‘,PRIMARY KEY (`id`))COMMENT=‘职工表‘ENGINE=InnoDB;
INSERT INTO `staff` (`id`, `name`, `salary`, `depart`) VALUES (1, ‘小李‘, 5000, ‘it部门‘);INSERT INTO `staff` (`id`, `name`, `salary`, `depart`) VALUES (3, ‘小红‘, 3000, ‘人事‘);INSERT INTO `staff` (`id`, `name`, `salary`, `depart`) VALUES (2, ‘张三‘, 2000, ‘财务‘);以下有几种查询sql:
1、select a.depart, sum(a.salary) from staff a group by a.depart;
技术分享

2、select a.depart, concat("price=" , sum(a.salary)) from staff a group by a.depart;
技术分享

3、select a.depart, group_concat("depart=",a.depart, ",price=", sum(a.salary)) from staff a group by a.depart;

技术分享

4、以上可以更改为:

select group_concat("depart=",c.depart, " price=",c.result) from staff a leftjoin (select b.depart, sum(b.salary) as result from staff b group by b.depart) as c on c.depart = a.depart;

技术分享

 

在网上看到一种说法:The SUM and AVG functions are not procedural. They are done in parallel. Which means that they can‘t be dependent.

总结一下,a、对于group_concat中使用聚合函数的情况,可以选择先计算聚合函数,再引用。b、可以对查询结果取别名

 

MySql中group_concat的使用