查询人数大于2的 部门,倒序排序
SELECT d.department_name, COUNT(*) ma FROM departments d,employees t
WHERE t.department_id = d.department_id
GROUP BY d.department_name HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;
HAVING 类似where,因为count函数不能用where,只能用HAVING
HAVING从句与GROUP BY从句一起使用,用于聚合条件的判断,如COUNT(*)>1,因聚合条件不能在WHERE指明;
join 方法
下面的是等同left join departments on t.department_id = d.department_id,下面的方法比较简洁
SELECT t.employee_id, t.first_name, d.department_name FROM employees t , departments d
WHERE t.department_id = d.department_id(+) ;
left join 是保留左边表的所有记录
UNION:
将多个SELECT语句的结果集加起来,去掉重复的数据(集合并运算)
UNION ALL:
将多个SELECT语句的结果集加起来,不去掉重复的数据
INTERSECT:
求多个SELECT的交集(集合交运算)
MINUS:
求在第一个SELECT,不在第二个SELECT的数据(集合减运算)
BETWEEN AND:
SELECT * FROM employees WHERE Rownum BETWEEN 1 AND 7;