mysql 分组查询
书写顺序
select * from table where salary >200 group by dept having manage > order by id limit 0,5
创建表 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(salary ) , if(salary>1500,COUNT(salary),'没有') from employee 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;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析