首页 > 代码库 > 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的使用
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。