SQL高级查询之一

SELECT e.emp_id, e.fname, e.lname FROM (SELECT emp_id, fname, lname, start_date, title FROM employee ) e; 二，从视图中查询 CREATE VIEW employee...

SELECT e.emp_id, e.fname, e.lname FROM (SELECT emp_id, fname, lname, start_date, title FROM employee ) e;

二，从视图中查询

CREATE VIEW employee_vw AS

 - SELECT emp_id, fname, lname,

 - YEAR(start_date) start_year

 - FROM employee;

SELECT emp_id, start_year

 - FROM employee_vw;

三，联表查询

SELECT e.emp_id, e.fname, e.lname, d.name dept_name FROM employee AS e INNER JOIN department AS d ON e.dept_id = d.dept_id;

四，WHERE子句配逻辑表达式

SELECT emp_id, fname, lname, start_date, title FROM employee WHERE title = Head Teller OR start_date 2002-01-01;

五，GROUP BY子句配合HAVING使用

SELECT d.name, count(e.emp_id) num_employees FROM department d INNER JOIN employee e ON d.dept_id = e.dept_id GROUP BY d.name HAVING count(e.emp_id) 2;

六，ORDER BY排序使用内置函数

SELECT cust_id, cust_type_cd, city, state, fed_id FROM customer ORDER BY RIGHT(fed_id, 3) DESC;