简述:
找出部门人数大于或等于10个人的部门。
要点
在上一篇的基础上,考察having的使用。上篇链接:http://blog.youkuaiyun.com/lovesummerforever/article/details/78806765
1、函数count() + group by。
2、having 字句查询。
3、建立的表结构,以及添加的数据如下sql。
-- ----------------------------
-- Table structure for employee_info
-- ----------------------------
DROP TABLE IF EXISTS `employee_info`;
CREATE TABLE `employee_info` (
`employee_id` int(10) NOT NULL,
`department_id` int(10) NOT NULL,
PRIMARY KEY (`employee_id`,`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of employee_info
-- ----------------------------
INSERT INTO `employee_info` VALUES ('1', '2');
INSERT INTO `employee_info` VALUES ('2', '2');
INSERT INTO `employee_info` VALUES ('3', '2');
INSERT INTO `employee_info` VALUES ('4', '1');
INSERT INTO `employee_info` VALUES ('5', '1');
INSERT INTO `employee_info` VALUES ('6', '2');
INSERT INTO `employee_info` VALUES ('7', '2');
INSERT INTO `employee_info` VALUES ('8', '2');
INSERT INTO `employee_info` VALUES ('9', '2');
INSERT INTO `employee_info` VALUES ('10', '2');
INSERT INTO `employee_info` VALUES ('11', '2');
INSERT INTO `employee_info` VALUES ('12', '2');
INSERT INTO `employee_info` VALUES ('13', '3');
INSERT INTO `employee_info` VALUES ('14', '3');
INSERT INTO `employee_info` VALUES ('15', '3');
INSERT INTO `employee_info` VALUES ('16', '3');
INSERT INTO `employee_info` VALUES ('17', '3');
INSERT INTO `employee_info` VALUES ('18', '3');
INSERT INTO `employee_info` VALUES ('19', '3');
INSERT INTO `employee_info` VALUES ('20', '3');
INSERT INTO `employee_info` VALUES ('21', '3');
INSERT INTO `employee_info` VALUES ('22', '3');
INSERT INTO `employee_info` VALUES ('23', '3');
思路
1)首先是根据部门id汇总部门人数。
select COUNT(employee_id) as number ,department_id from employee_info group by department_id
2)group by后面不能用where , 可以用having字句,所以最终sql如下。
select COUNT(employee_id) as number ,department_id from employee_info group by department_id HAVING number>=10;
总结:
路漫漫其修远兮,吾将上下而求索。
如果遇到这样的题,应该把大的问题一个个分散成小问题,然后一个个的解决小问题,然后拼凑起来,大问题就可以解决了。