5154

MySQL 复杂查询实例

一、查询多表关联数据

在实际应用中，数据库中的表往往存在关联关系，有一个orders 表（订单表）和customers 表（客户表），orders 表中有customer_id 字段用于关联customers 表的id 字段，现在要查询每个客户的姓名以及他们对应的订单编号和订单金额，可以使用如下的 SQL 语句：

SELECT c.name AS customer_name, o.order_number, o.amount AS order_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

这段代码通过JOIN 关键字将customers 表和orders 表连接起来，根据customer_id 进行匹配，然后选择出需要的字段，其中c 是customers 表的别名，o 是orders 表的别名，这样可以方便地引用字段。

二、分组与聚合查询

假设有一个sales 表（销售记录表），包含product_id（产品 ID）、quantity（销售数量）和price（单价）等字段，要查询每种产品的销售总额和销售数量，可以使用以下查询：

SELECT product_id, SUM(quantity * price) AS total_sales, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

这里使用了GROUP BY 子句按照product_id 对记录进行分组，然后使用聚合函数SUM 计算每个产品的销售总额（销售数量乘以单价的总和）和销售数量。

三、子查询的使用

考虑一个场景，想要查找订单金额高于公司平均订单金额的所有订单信息，首先需要计算平均订单金额，然后再筛选出高于此平均值的订单，可以使用子查询来实现：

SELECT order_id, customer_id, order_date, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

内部的那个查询SELECT AVG(amount) FROM orders 就是子查询，它先计算出所有订单的平均金额，外部查询则根据这个平均金额筛选出符合条件的订单记录。

四、联合子查询与多表查询

有一个employees 表（员工表）和一个departments 表（部门表），employees 表中有department_id 关联departments 表的id，现在要查询每个部门的名称以及该部门中工资最高的员工姓名和工资，可以这样写查询语句：

SELECT d.name AS department_name, e.name AS employee_name, e.salary AS highest_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);

这个查询先通过内部子查询SELECT MAX(salary) FROM employees WHERE department_id = e.department_id 找到每个部门的最高工资，然后在外部查询中根据这个最高工资筛选出对应的员工和部门信息，并通过JOIN 将employees 表和departments 表连接起来获取部门名称。

相关问题与解答

问题一：如果在多表关联查询中，某个表的连接条件是模糊匹配，应该如何修改查询语句？

解答：如果连接条件是模糊匹配，比如在orders 表和customers 表关联时，不是精确匹配customer_id，而是根据customer_name 的部分匹配来关联，可以使用LIKE 运算符。

SELECT c.name AS customer_name, o.order_number, o.amount AS order_amount
FROM customers c
JOIN orders o ON c.name LIKE CONCAT('%', o.customer_name, '%');

这里假设orders 表中也有customer_name 字段用于模糊匹配，使用CONCAT 函数和通配符% 来进行模糊查询。

问题二：在分组聚合查询中，如果想要对聚合结果进行排序，应该怎么做？

解答：可以在聚合查询的基础上使用ORDER BY 子句，对于前面查询每种产品销售总额和销售数量的例子，如果想要按照销售总额从高到低排序，可以这样写：

SELECT product_id, SUM(quantity * price) AS total_sales, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id
ORDER BY total_sales DESC;

这样就会根据total_sales（销售总额）以降序的方式对查询结果进行排序。