mysql子查询

一、where或having后面
二、select后面

#查询每个部门的员工个数(有的部门没有员工)
/departments作主表 employees作从表 按departments.department_id分组/

SELECT COUNT(*),departments.department_id
FROM departments
LEFT JOIN employees ON departments.department_id=employees.department_id
GROUP BY departments.department_id
ORDER BY departments.department_id ASC
#必须以departments.department_id做分组 因为employee.department_id只是有员工的部门

/**/

SELECT departments.,(SELECT COUNT()
FROM employees
WHERE departments.department_id=employees.department_id
) AS ncount#对departments.*中每一个记录 在employees表中找所在部门号的员工计数
FROM departments
ORDER BY departments.department_id ASC