MySQL:至少参与xxx参与的全部事件(二)

文章访问量:

更多限制条件的嵌套NOT EXISTS

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

头歌例题

仔细审题

任务描述

创建一个名为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中选出参与了某个参与的结果集A中所有比赛的选手,得到结果集B;
  • 最后再对B进行筛选出参与了三次比赛的选手得到结果集C。再对C进行SELECT即可。

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

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

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;
Subscribe
提醒
0 评论
Inline Feedbacks
View all comments
0
在此留下你的评论x