mysql实现多表关联统计(子查询统计)示例

下面我详细讲解一下“mysql实现多表关联统计(子查询统计)示例”的完整攻略。

什么是多表关联统计？

在关系型数据库中，有时候需要对多个数据表进行统计分析或者计算。此时，就需要使用多表关联查询来实现。多表关联查询就是通过有相同字段连接多张表并将结果集合并在一起，实现更全面、详细的分析效果。

如何通过子查询实现多表关联统计？

在mysql中，我们通过子查询即可实现多表关联统计。具体地，我们需要将需要关联的两个表分别作为主表和子查询表，先从子查询表中找到符合要求的记录ID，在主表中根据这些ID来进行统计计算。具体实现过程如下：

1. 以商品表和订单表为例，我们需要统计出每种商品的销售数量和其总销售额。

   首先，在订单表中统计每种商品的销售数量和总销售额：

   SELECT goods_id, SUM(num) AS total_num, SUM(price*num) AS total_price
FROM order
GROUP BY goods_id;

   这个查询将得到一个结果集，包括商品ID、销售数量和总销售额。

2. 接下来，我们需要通过子查询将商品表和上一步查询出来的结果集关联起来，以得到每个商品的名称和价格。

   SELECT name, price, total_num, total_price
FROM goods
INNER JOIN
(SELECT goods_id, SUM(num) AS total_num, SUM(price*num) AS total_price
FROM order
GROUP BY goods_id) AS orders
ON goods.id = orders.goods_id;

   在这个查询中，我们使用了子查询将订单表和商品表关联起来。内部查询通过SUM函数统计出每种商品的销售数量和总销售额，外部查询再将商品表和内部查询的结果集以商品ID作为连接条件进行关联，即得到每种商品的名称和价格以及其销售数量和总销售额。

3. 最后，我们可以根据需要在外部查询中对结果集进行进一步的处理和筛选，以达到特定的统计分析效果。

示例说明

下面，我通过两个具体的示例，进一步说明如何通过子查询实现多表关联统计：

示例一：查询每个部门的平均薪资和薪资总和

假设我们有两张表：员工表（employee）和部门表（department），它们之间通过部门ID进行关联。我们需要查询每个部门的平均薪资和薪资总和。

在这个问题中，我们需要先在employee表中统计每个部门的薪资总和和人数，然后通过这些数据计算每个部门的平均薪资。我们可以通过以下SQL查询语句实现：

SELECT department.name, AVG(salary) as avg_salary, SUM(salary) as sum_salary
FROM employee
INNER JOIN department
ON employee.department_id = department.id
INNER JOIN
    (SELECT department_id, COUNT(*) as count, SUM(salary) as total_salary
     FROM employee
     GROUP BY department_id) AS sub
ON department.id = sub.department_id
GROUP BY department.id;

在这个查询中，首先通过内部查询先从employee表中查询出每个部门的薪资总和和人数，然后在外部查询中和department表进行关联，以得到每个部门的名称、平均薪资和薪资总和。

示例二：查询每个学生已选的课程数目和总学分

假设我们有三张表：学生表（student）、课程表（course）和选课表（selection），它们之间通过学生ID和课程ID分别进行关联。我们需要查询每个学生已选的课程数目和总学分。

在这个问题中，我们需要先在selection表中统计每个学生选了多少门课程，并计算每门课程的学分，然后通过这些数据计算每个学生已选的课程数目和总学分。我们可以通过以下SQL查询语句实现：

SELECT student.name, COUNT(*) as course_num, SUM(course.credit) as total_credit
FROM student
INNER JOIN selection
ON student.id = selection.student_id
INNER JOIN course
ON selection.course_id = course.id
GROUP BY student.id;

在这个查询中，我们通过INNER JOIN将student表、selection表和course表关联起来，内部查询统计出每个学生选了多少门课程和每门课程的学分，然后在外部查询中以学生ID作为分组条件，求出每个学生已选的课程数目和总学分。

希望这些例子可以帮助您更好地理解如何通过子查询实现多表关联统计。