sql高级查询50道题

SQL是关系型数据库管理系统的标准语言，它的使用被广泛应用于企业级应用程序中。随着数据量和数据复杂度的不断增加，SQL的高级查询也成为了日常工作中的必备技能之一。本文将通过50道SQL高级查询题目的分析，对SQL高级查询理解提供帮助。

一、LIKE语法的理解

1. 查找所有以字母“a”开头且长度为3的单词

SELECT * FROM words WHERE word LIKE 'a__';

解析：LIKE语法用于匹配字符串，在此处，'%'作为通配符，'_'表示匹配任何单个字符。

2. 查找所有以字母“a”结尾的单词

SELECT * FROM words WHERE word LIKE '%a';

解析：在此处，'%'前缀表示匹配所有不确定字符，'$'后缀表示匹配以'a'结尾的字符。

3. 查找所有包含字母“a”且长度为5到7的单词

SELECT * FROM words WHERE word LIKE '%a%' AND LENGTH(word) BETWEEN 5 AND 7;

解析：在此处，'%'通配符用于找到包含字符“a”的单词，LENGTH函数用于确定单词长度的范围。

二、聚合函数的应用

4. 查找最高工资的员工信息

SELECT * FROM employees WHERE salary=(SELECT MAX(salary) FROM employees);

解析：MAX函数用于返回整张表中最高的salary值，然后将其作为子查询的参数，从而找到具有最高salary值的员工信息。

5. 查找平均工资超过8000的部门

SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary)>8000;

解析：这里使用GROUP BY关键字，根据部门对工资进行分组，然后在HAVING子句中过滤掉平均工资小于8000的部门。

6. 查找每个部门最高工资的员工

SELECT * FROM employees WHERE (department, salary) IN (SELECT department, MAX(salary) FROM employees GROUP BY department);

解析：这里使用GROUP BY查询每个部门的最高工资，然后将其作为子查询中的查询参数根据需要输出相关的员工信息。

三、表联结和子查询

7. 查找所有有过销售记录的客户姓名

SELECT DISTINCT customers.name FROM customers JOIN orders USING (customer_id);

解析：DISTINCT用于过滤重复的客户姓名，JOIN连接表customers和orders，并在USING子句中使用customer_id作为相同列。

8. 查找所有未进行过订单的客户姓名

SELECT customers.name FROM customers LEFT JOIN orders USING (customer_id) WHERE order_id IS NULL;

解析：在LEFT JOIN子句中，使用customer_id连接表customers和orders，而WHERE子句中的order_id是一个空值，即未进行过订单的客户。

9. 查找销售额排名前10的产品

SELECT product_name, SUM(quantity*price) AS sales FROM order_details JOIN products USING (product_id) GROUP BY product_id ORDER BY sales DESC LIMIT 10;

解析：首先使用JOIN连接表order_details和products，并在GROUP BY子句中按product_id分组以计算每个产品的销售额，然后对结果进行降序排序并限制前10行。

四、窗口函数的应用

10. 计算每位员工的平均工资，并显示高于平均值的员工信息

SELECT * FROM (SELECT *, AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary FROM employees) t WHERE salary>avg_dept_salary;

解析：这里使用AVG()窗口函数计算每个员工所在部门的平均工资，并借助子查询将结果存储在临时表t中，然后选择工资高于部门平均工资的员工信息。

11. 计算每个月的订单总额以及前一个月的订单总额

WITH monthly_sales AS (SELECT DATE_FORMAT(order_date,'%Y-%m') AS month, SUM(quantity*price) AS sales FROM orders JOIN order_details USING (order_id) GROUP BY month),

prior_month_sales AS (SELECT month, sales, LAG(sales) OVER (ORDER BY month) AS prior_sales FROM monthly_sales)

SELECT * FROM prior_month_sales;

解析：首先使用WITH子句定义两个子查询，分别计算每个月的订单总额以及前一个月的订单总额。然后，使用LAG()窗口函数计算prior_month_sales中的前一个月总销售额。

五、临时表和视图

12. 创建一个临时表，存储每个员工按照岗位分组后的平均工资

CREATE TEMPORARY TABLE temp_emp_salary AS SELECT job_title, AVG(salary) AS avg_salary FROM employees GROUP BY job_title;

解析：在CREATE TABLE语句之前加上关键字TEMPORARY可以创建一个临时表，用于存储某些临时数据。上述示例创建的临时表temp_emp_salary包含每个职位的平均工资。

13. 创建一个视图，包含每个员工姓名以及他们所在部门的经理姓名

CREATE VIEW view_emp_manager AS SELECT employees.name AS employee_name, managers.name AS manager_name FROM employees JOIN departments ON employees.department_id = departments.department_id JOIN employees AS managers ON departments.manager_id=managers.employee_id;

解析：视图是一种虚拟表，其内容基于指定的SELECT查询。上述示例使用JOIN语法和别名，将employees、departments和managers表中的信息合并到一个view_emp_manager视图中。