mysql 多表分组排序_mysql 等值连接案例之添加筛选、分组、排序、多表

加筛选

#案例:有奖金的员工名和部门名

SELECT

last_name,department_name

FROM

employees AS e,departments AS d

WHERE

e.department_id=d.department_id

AND

e.commission_pct IS NOT NULL;

#案例：查询城市名中第二个字符为o的部门名和城市名

SELECT

department_name,city

FROM

departments AS d,locations AS l

WHERE

d.location_id=l.location_id

AND

l.city LIKE'_o%';

加分组

#案例:查询每个城市的部门个数

SELECT

COUNT(*),city

FROM

departments AS d,locations AS l

WHERE

d.`location_id`=l.`location_id`

GROUP BY

city;

#案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT

department_name,d.manager_id,MIN(salary)

FROM

employees AS e,departments AS d

WHERE

d.department_id=e.department_id

AND

commission_pct IS NOT NULL

GROUP BY

department_name,d.manager_id;

加排序

#案例:查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT

job_title,COUNT(*)

FROM

employees AS e,jobs AS j

WHERE

e.job_id=j.job_id

GROUP BY

job_title

ORDER BY

COUNT(*) DESC;

三表连接

#案例：查询员工名 、部门名、所在的城市

SELECT

last_name,department_name,city

FROM

employees AS e,departments AS d,locations AS l

WHERE

e.department_id=d.`department_id`

AND

d.`location_id`=l.location_id