高效合并复杂SQL查询：学生实验统计案例分析

本文链接：https://blog.youkuaiyun.com/SSHLY3/article/details/148867946

现在有两个统计学生的实验，从不同的角度统计执行情况和成功率，因为后台关联实验结果表的关联关系不一样，所以用两个SQL先查了两个表格,现在想将其进行合并，因为都是from的学生表，都是对每个总的合并效果

下面这是统计各个学员在这个时间段内应参与的总实验次数、时间段内操作实验次数、时间段内未操作实验次数


SELECT
stu.id,
stu.tfc_name ,
COUNT( DISTINCT te.id ) AS allCount, --总实验次数
SUM( CASE WHEN tr.test_id IS NOT NULL THEN 1 ELSE 0 END ) AS executionCount,  --时间段内操作实验次数
SUM( CASE WHEN tr.test_id IS NULL THEN 1 ELSE 0 END ) AS unexecutionCount   --时间段内未操作实验次数
FROM
tfc_student stu
LEFT JOIN tfc_class cl ON stu.id = cl.stu_id   --关联班级表
LEFT JOIN tfc_test te ON cl.id = te.classid    --关联实验表
LEFT JOIN tfc_test_remarks tr ON te.id = tr.test_id   --关联实验结果表
        WHERE
DATE_FORMAT( te.insuranceetime, '%Y-%m-%d' ) BETWEEN "2022-05-23 14:49:04"
AND "2023-06-23 14:49:04"
GROUP BY
stu.id,
stu.stu_name
ORDER BY
stu.id

下面这是统计各个学员在这个时间段内的操作实验次数、实验成功次数、实验成功率


SELECT
stu.id,
stu.stu_name ,
COUNT( DISTINCT te.id ) AS renewalCount,--操作实验次数
COUNT( DISTINCT tr.test_id ) AS successCount,--实验成功次数
CONCAT(
        ROUND(IFNULL( COUNT( DISTINCT te.id ) / COUNT(DISTINCT tr.test_id ) * 100, 0 ), 2 ),
        '%'
        ) AS successRate  --实验成功率
FROM
tfc_student stu
JOIN sys_user su ON stu.user_id = su.user_id  --关联用户表
LEFT JOIN tfc_class cl ON stu.id = cl.stu_id   --关联班级表
LEFT JOIN  tfc_test te ON cl.id = te.classid  --关联实验表
AND DATE_FORMAT( te.create_time, '%Y-%m-%d' ) BETWEEN "2023-05-23 14:49:04"
AND "2024-06-23 14:49:04"
LEFT JOIN tfc_test_remarks tr ON su.user_name = tr.create_by  --关联实验结果表
AND DATE_FORMAT( tr.create_time, '%Y-%m-%d' ) BETWEEN "2023-05-23 14:49:04"
AND "2024-06-23 14:49:04"
GROUP BY
stu.id,
stu.stu_name
ORDER BY
successRate DESC;

可以发现，第一个查询统计了学生的总实验次数、时间段内操作实验次数和未操作实验次数。第二个查询统计了操作实验次数、实验成功次数和实验成功率。合并思路科研将两个查询作为子查询，然后通过学生ID进行关联。

注意点：

1. 第一个查询的时间条件字段是`te.executiontime`，而第二个查询的时间条件字段是`te.create_time`和`tr.create_time`。

2. 第一个查询中使用了`tfc_test_remarks`表来标记是否操作（通过tr.test_id是否存在），第二个查询中使用了同样的表来统计成功次数（通过tr.test_id计数）。

3. 第二个查询中关联了`sys_user`表，而第一个没有。

下面将分别构建两个子查询，然后通过学生ID进行左连接。注意，第一个查询中分组字段包含`stu.id`和`stu.stu_name`，第二个也是，因此可以用学生ID进行关联。

修正后的第一个查询：

SELECT

stu.id,

stu.stu_name,

...

注意：两个查询的时间条件不同，第一个查询的时间条件在WHERE子句（作用于te.executiontime），第二个查询的时间条件在JOIN的ON子句（分别作用于te.create_time和tr.create_time）。在合并时，保持各自的时间条件。

另外，第二个查询中的续保率计算可能存在除数为0的问题，使用NULLIF来避免。

由于两个查询的时间条件可能不同（第一个查询使用一个时间范围，第二个查询使用另一个时间范围），所以在子查询中分别使用各自的参数。但根据原查询，它们的时间范围是硬编码的，且不同。在合并后，可能需要使用不同的参数。这里假设在合并后，两个子查询的时间条件参数是独立的。

为了参数化，需要为两个子查询分别使用不同的参数：

第一个子查询：startDate1, endDate1

第二个子查询：startDate2, endDate2

如果实际业务中两个子查询的时间条件相同，那么可以使用相同的参数。根据这个思路下面写合并的SQL：注意：第一个子查询中，由于使用了WHERE条件，它会过滤掉没有实验记录的学生（因为LEFT JOIN后，如果没有实验记录，则te.id为NULL，而WHERE条件要求te.executiontime在指定范围内，所以这些学生会被过滤掉）。如果希望保留所有学生，即使没有实验记录，那么需要将第一个子查询的时间条件移到LEFT JOIN的ON子句中。但原查询就是使用WHERE，所以保持原逻辑（即只统计有实验记录且在时间范围内的学生）。但是，原第一个查询中，使用了LEFT JOIN，然后WHERE条件中使用了te.executiontime，这会导致将没有实验记录或实验记录不在时间范围内的学生排除。所以，如果想保留所有学生，即使他们没有实验记录，那么需要将条件移到ON子句。但原查询不是这样，所以按照原查询逻辑。根据需求，这里按照原查询逻辑。合并后的SQL：

SELECT

t1.id,

t1.stu_name,

t1.allCount,

t1.executionCount,

t1.unexecutionCount,

t2.renewalCount,

t2.successCount,

t2.successRate

FROM

(SELECT

stu.id,

stu.stu_name,

COUNT(DISTINCT te.id) AS allCount,

SUM(CASE WHEN tr.test_id IS NOT NULL THEN 1 ELSE 0 END) AS executionCount,

SUM(CASE WHEN tr.test_id IS NULL THEN 1 ELSE 0 END) AS unexecutionCount

FROM

tfc_student stu

LEFT JOIN tfc_class cl ON stu.id = cl.stu_id

LEFT JOIN tfc_test te ON cl.id = te.classid

LEFT JOIN tfc_test_remarks tr ON te.id = tr.test_id

WHERE

DATE_FORMAT(te.executiontime, '%Y-%m-%d') BETWEEN #{startDate1} AND #{endDate1}

GROUP BY

stu.id,

stu.stu_name) t1

LEFT JOIN

(SELECT

stu.id,

stu.stu_name,

COUNT(DISTINCT te.id) AS renewalCount,

COUNT(DISTINCT tr.test_id) AS successCount,

CONCAT(

ROUND(IFNULL(COUNT(DISTINCT te.id) / NULLIF(COUNT(DISTINCT tr.test_id), 0) * 100, 0), 2),

'%'

) AS successRate

FROM

tfc_student stu

JOIN sys_user su ON stu.user_id = su.user_id

LEFT JOIN tfc_class cl ON stu.id = cl.stu_id

LEFT JOIN tfc_test te ON cl.id = te.classid

AND DATE_FORMAT(te.create_time, '%Y-%m-%d') BETWEEN #{startDate2} AND #{endDate2}

LEFT JOIN tfc_test_remarks tr ON su.user_name = tr.create_by

AND DATE_FORMAT(tr.create_time, '%Y-%m-%d') BETWEEN #{startDate2} AND #{endDate2}

GROUP BY

stu.id,

stu.stu_name) t2

ON t1.id = t2.id

ORDER BY t1.id;

```

注意：

1. 在第二个子查询中，使用了`NULLIF`来避免除数为0的情况。

2. 第一个子查询中，保留了原查询的WHERE条件，所以只包括在指定时间范围内有实验记录的学生。

3. 第二个子查询中，时间条件在JOIN的ON子句，所以即使学生没有在时间范围内的实验记录，也会被包括（但计数为0）。

4. 两个子查询都按学生ID和姓名分组。

5. 最终结果按t1.id排序。

如果两个子查询的时间条件相同（即使用相同的开始和结束时间），那么可以使用相同的参数名，例如都使用`#{startDate}`和`#{endDate}`。

这是最终效果