题解 | #根据指定记录是否存在输出不同情况#

# 把 当有任意一个0级用户未完成试卷数大于2 作为一个条件，临时表，如果有，那统计数量是大于0的
# 总体上输出全部用户的指标，如果是第一种情况，就只筛选出等级为0的

# 统计是否有满足条件的
WITH temp_0 AS(
SELECT uid
FROM user_info 
LEFT JOIN exam_record USING(uid)
WHERE level = 0 # 选择0级用户
GROUP BY uid
HAVING SUM(IF(score, 0, 1)) > 2 # 未完成的记录数大于2
)


SELECT uid, 
	# 未完成数计算，这里要判断是否有开始的记录，如果没有，就说明没有做这个，要置为0
    SUM(IF(score IS NULL AND start_time IS NOT NULL, 1, 0)) incomplete_cnt,  
	# 未完成率 = 未完成数/开始作答次数，为了不让分母为0，所以使用if，其实只要给个数就行，这样在没有开始记录的时候也给个数，分子始终为0，计算出来也是0
    ROUND(SUM(IF(score IS NULL AND start_time IS NOT NULL, 1, 0)) / COUNT(IF (start_time, start_time, 0)), 3) incomplete_rate
FROM user_info a
# 左外联结，这是为了防止有些用户没有记录
LEFT JOIN exam_record b USING(uid)
# 使用where语句控制开关，
# 使用EXISTS谓词判断是否存在满足条件的，如果至少有一个满足条件的，那就选择所有等级为0的用户
WHERE (
    EXISTS(SELECT 1 FROM temp_0) AND a.level = 0)
    OR
	# 没有满足条件的，并且有开始记录的（防止没有作答记录的其余用户也计算进来）
    (NOT EXISTS(SELECT 1 FROM temp_0) AND b.start_time IS NOT NULL)
GROUP BY uid
ORDER BY incomplete_rate

踩的坑：1.临时表选择临时用户忘了加分组

2.未完成数计算要加上检测是否有作答开始记录

3.未完成率计算要给分母一个常数，如果是null，结果也会是null

4.使用exists关键字来判断是否存在和不存在

新知识点：

1.当然，我很乐意为您解释这个WHERE子句的逻辑。

在这个WHERE子句中，我们有两个主要的条件，它们通过OR连接。这意味着如果任何一个条件为真，相应的行就会被选中。让我们分别看看这两个条件：

1. EXISTS(SELECT 1 FROM temp_0) AND a.level = 0:EXISTS(SELECT 1 FROM temp_0): 这是一个EXISTS子查询，它检查temp_0中是否有任何行。temp_0是之前定义的临时表，用于查找所有0级用户中未完成试卷数大于2的用户。如果至少有一个这样的用户，那么EXISTS(SELECT 1 FROM temp_0)的结果为真（TRUE）。a.level = 0: 这是一个简单的条件，检查用户信息表（user_info）中用户的等级是否为0。这个复合条件意味着如果存在至少一个0级用户的未完成试卷数大于2，那么查询将选择所有0级用户的信息，不论这些用户是否有作答记录。
2. NOT EXISTS(SELECT 1 FROM temp_0) AND b.start_time IS NOT NULL:NOT EXISTS(SELECT 1 FROM temp_0): 这是EXISTS的相反情况。它检查是否没有任何0级用户的未完成试卷数大于2。如果没有这样的用户，那么此条件为真。b.start_time IS NOT NULL: 这个条件检查exam_record表中是否存在作答记录。只有当用户至少开始了一次作答，这个条件才为真。当上述两个条件都为真时，这意味着如果没有任何0级用户的未完成试卷数大于2，那么查询将选择所有至少有作答记录的用户的信息。

综上所述，这个WHERE子句的逻辑确保了当至少有一个0级用户的未完成试卷数大于2时，只统计0级用户的信息。如果没有这样的用户，那么统计所有有作答记录的用户的信息。这样的设计符合您之前描述的需求。

2.在EXISTS(SELECT 1 FROM temp_0)这个表达式中，SELECT 1是一种常见的SQL技巧，特别是在与EXISTS子句一起使用时。

在这个上下文中，SELECT 1的目的不是为了选择表中的某个具体列，而是仅仅用来检查是否存在符合特定条件的行。EXISTS子句的作用是检查嵌套查询（在这里是SELECT 1 FROM temp_0）是否返回至少一个行。如果返回至少一个行，EXISTS就返回TRUE；如果没有返回任何行，EXISTS返回FALSE。

因此，在这个表达式中：

• SELECT 1只是一个简单的方式来说“我只关心是否有行存在，而不关心这些行的具体内容”。
• FROM temp_0指定了要检查的表或临时表。
• 结合起来，EXISTS(SELECT 1 FROM temp_0)就是在检查temp_0中是否有任何行存在。

这种方法之所以有效，是因为EXISTS只关心是否有数据行，而不关心这些数据行的具体值。所以，SELECT 1在这里是一个高效且常用的方法，尤其是在只需要检查行的存在性时。