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;
复制代码

 

posted @   系统显示名称已被使用  阅读(45)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
点击右上角即可分享
微信分享提示