mysql 分组查询

创建表 employee

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `performance` double(255,0) DEFAULT NULL,
  `manage` double(255,0) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee` VALUES (1001, '张三', '男', '1991-7-25', 2000, 200, 500, '营销部');
INSERT INTO `employee` VALUES (1002, '李四', '男', '2017-7-5', 4000, 500, NULL, '营销部');
INSERT INTO `employee` VALUES (1003, '王五', '女', '2018-5-1', 6000, 100, 5000, '研发部');
INSERT INTO `employee` VALUES (1004, '赵六', '男', '1991-6-1', 1000, 3000, 4000, '财务部');
INSERT INTO `employee` VALUES (1005, '孙七', '女', '2018-3-23', 8000, 1000, NULL, '研发部');
INSERT INTO `employee` VALUES (1006, '周八', '男', '2010-9-8', 5000, 500, 1000, '人事部');
INSERT INTO `employee` VALUES (1007, '吴九', '女', '2017-7-5', 8000, 601, NULL, '研发部');
INSERT INTO `employee` VALUES (1008, '郑十', '女', '2014-4-6', 4000, 1801, NULL, '人事部');

对所有员工的薪水进行排序
SELECT * from employee  ORDER BY salary desc;
查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT * from employee ORDER BY salary desc,id asc;
查询employee表中记录数：
SELECT count(*) as '总数' FROM employee;
查询员工表中有管理费的人数
SELECT * from employee where manage is not NULL;
SELECT count(manage) from employee;
查询员工表中月薪大于2500的人数：
SELECT  * from employee WHERE salary >2500;
统计月薪与绩效之和大于5000元的人数：
SELECT * from employee where IFNULL(salary,0)+IFNULL(performance,0)>5000;
查询有绩效的人数，和有管理费的人数：
SELECT COUNT(performance),COUNT(manage) from employee;

查询每个部门的部门名称和每个部门的工资和
SELECT department,GROUP_CONCAT(salary),sum(salary) from employee GROUP BY department;
查询每个部门的部门名称以及每个部门的人数
SELECT department,COUNT(*) from employee GROUP BY department;
查询每个部门的部门名称以及每个部门工资大于1500的人数
SELECT department,COUNT(*) from employee WHERE salary >1500 GROUP BY department;

查询工资大于1500人数按照部门分组
SELECT department,GROUP_CONCAT(name) from employee WHERE salary >1500 GROUP BY department;
SELECT department,GROUP_CONCAT(name),salary from employee GROUP BY department HAVING salary>1500; #having 字段必须select 出来

查询个人工资大于2000，部门工资和>6000的部门名称
SELECT department,GROUP_CONCAT(salary),SUM(salary) as total FROM employee WHERE salary>2000  GROUP BY department HAVING total >5000;

查询个人工资、绩效、管理费之和大于7000的人员姓名，按照部门进行分类。
SELECT department,GROUP_CONCAT(name) from employee 
where IFNULL(salary,0)+IFNULL(performance,0)+IFNULL(manage,0)>7000 
GROUP BY department; 

SELECT department,GROUP_CONCAT(name) from (
    SELECT  *,IFNULL(salary,0)+IFNULL(performance,0)+IFNULL(manage,0) as total  from employee   HAVING total>7000
)as newt  GROUP BY department;