MySQL：至少参与xxx参与的全部事件（二）

MySQL：至少参与xxx参与的全部事件（二） – WhiteNight's Site

标签：MySQL

本来不难的，结果实验课上又没能当场做出来。还是回到宿舍复盘才看到问题所在，令人感慨。

头歌例题

仔细审题

任务描述

创建一个名为test的视图，查询这样的选手信息(users表)，参加过三次比赛(不包含null)，且至少解答过”202002020217″选手result为4且采用language为1解答过的所有题目。

提示：count(distinct 列名)：根据指定的列统计记录总数，去重复且不包括NULL值。

相关知识

1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答 solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

解题思路

看题

我当时看到这题，嘿，又是嵌套NOT EXISTS，这个我熟啊，两分钟给你写完。视图直接AS就行了，没啥影响。然后这是我当时写的SQL

CREATE VIEW test 
AS 
SELECT A.user_id,A.reg_time,A.name 
FROM users AS A
JOIN solution AS B
ON A.user_id=B.user_id
WHERE NOT EXISTS(
    SELECT 1 
    FROM solution AS C 
    WHERE C.user_id='202002020217' AND C.result=4 AND C.language=1 
    AND NOT EXISTS( 
        SELECT 1 
        FROM solution AS D 
        WHERE A.user_id=D.user_id AND C.contest_id=D.contest_id ) 
)
GROUP BY A.user_id
HAVING COUNT(DISTINCT B.contest_id)=3;

然后点了下提交，嗯？居然没过，这是怎么回事呢？然后开始检查。

参与了3场比赛和参与了xxx参与的全部比赛是并列关系；参与了xxx参与的比赛相比于上次新增了几个限制条件。

那思路就是先筛选出xxx参与的所有比赛（加上几个限制条件）得到结果集A；再从A中选出参与了xxx参与的所有比赛的选手，得到结果集B；最后再对B进行筛选选出参与了三次比赛的选手得到结果集C。最后对C进行SELECT即可。

也没其它的了，但是就是过不了，俺寻思这也没错啊，怎么回事呢？

然后这里调那里改，还是没过。越调越急。最后回到宿舍仔细复盘的时候发现，题目要的是problem_id，要的是xxx参与过的所有题目，而不是所有比赛…那改一下初次筛选的条件不就完事了….

CREATE VIEW test 
AS 
SELECT A.user_id,A.reg_time,A.name 
FROM users AS A
JOIN solution AS B
ON A.user_id=B.user_id
WHERE NOT EXISTS(
    SELECT 1 
    FROM solution AS C 
    WHERE C.user_id='202002020217' AND C.result=4 AND C.language=1 
    AND NOT EXISTS( 
        SELECT 1 
        FROM solution AS D 
        WHERE A.user_id=D.user_id AND C.problem_id=D.problem_id ) 
)
GROUP BY A.user_id
HAVING COUNT(DISTINCT B.contest_id)=3;