手机网站开发怎么样,网站开发 怎么才能发表情,常州知名网站公司,ui界面设计风格#xff08;用 exists 和 not exists可以求集合的交集(intersection)和差集(except)#xff0c;请参考另一份如名文档#xff09; #xff08;本文中所有SQL语句均在Mysql v5.1中验证#xff09; 初学SQL#xff0c;遇到exist和not exist#xff0c;看了一会弄明白了用 exists 和 not exists可以求集合的交集(intersection)和差集(except)请参考另一份如名文档 本文中所有SQL语句均在Mysql v5.1中验证 初学SQL遇到exist和not exist看了一会弄明白了结果发现还有他们的嵌套使用终于又懵了看了很长时间才摸出来个大概下面用几个例子分析一下 便于以后忘记时帮助理解。 题外首先说一下not exists的用法。 exists是存在一个即可返回真not exists是全都不存在才返回真 mysql select * from t1; ------------ | id | value1 | ------------ | 1 | 10 | | 2 | NULL | | 3 | 10 | | 4 | 3 | ------------ 4 rows in set (0.00 sec) mysql select * from t2; -------------------- | id | value1 | value2 | -------------------- | 1 | 10 | 100 | | 2 | NULL | 100 | | 3 | 5 | 80 | | 4 | 11 | 11 | | 6 | 5 | 80 | | 7 | 5 | 5 | -------------------- 6 rows in set (0.00 sec) mysql select * from t1 where not exists (select * from t2 where value1t1.value1); ------------ | id | value1 | ------------ | 2 | NULL | | 4 | 3 | ------------ 2 rows in set (0.00 sec) 进入正题 example 1参考 http://blog.csdn.net/mtawaken/article/details/6573122加以修改变成适合自己理解的方式 关系模式 student(学号stuid姓名stdname年龄age) course (课程号cid课程名cname学时hour) choosed(学号stuid课程号cid成绩score)代表学号为stuid的学生选了课程cid成绩为score mysql select * from student; ---------------------- | stuid | stdname | age | ---------------------- | 1 | Bob | 23 | | 2 | Jessy | 24 | | 3 | Ivy | 24 | | 4 | Grace | 25 | | 5 | Candise | 26 | | 6 | Angela | 25 | | 7 | Tracy | 27 | | 8 | Sun | 26 | | 9 | Kevin | 26 | | 10 | Richard | 26 | ---------------------- 10 rows in set (0.00 sec) mysql select * from course; --------------------- | cid | cname | hour | --------------------- | 101 | English | 108 | | 102 | Math | 136 | | 103 | Chinese | 110 | | 104 | Music | 48 | | 105 | Painting | 56 | --------------------- 5 rows in set (0.00 sec) mysql select * from choosed; -------------------- | stuid | cid | score | -------------------- | 1 | 101 | 80 | | 1 | 102 | 95 | | 1 | 103 | 88 | | 1 | 104 | 70 | | 1 | 105 | 98 | | 2 | 103 | 84 | | 2 | 105 | 79 | | 3 | 101 | 95 | | 3 | 102 | 84 | | 3 | 103 | 91 | | 3 | 104 | 85 | | 3 | 105 | 79 | | 4 | 101 | 94 | | 4 | 104 | 86 | | 6 | 101 | 85 | | 6 | 102 | 82 | | 6 | 103 | 91 | | 6 | 104 | 79 | | 6 | 105 | 81 | | 7 | 103 | 92 | | 10 | 102 | 87 | | 10 | 104 | 68 | -------------------- 22 rows in set (0.00 sec) 1)所有选了课至少一节的人的姓名 exists - exists mysql select stuid, stdname from student where exists (select * from course where exists (select * from choosed where stuidstudent.stuid and cidcourse.cid)); ---------------- | stuid | stdname | ---------------- | 1 | Bob | | 2 | Jessy | | 3 | Ivy | | 4 | Grace | | 6 | Angela | | 7 | Tracy | | 10 | Richard | ---------------- 7 rows in set (0.00 sec) 2)所有没选课一节课都没选的人的姓名 not exists - exists mysql select stuid, stdname from student where not exists (select * from course where exists (select * from choosed where stuidstudent.stuid and cidcourse.cid)); ---------------- | stuid | stdname | ---------------- | 5 | Candise | | 8 | Sun | | 9 | Kevin | ---------------- 3 rows in set (0.00 sec) 3)所有没选全部课程的人的姓名 exists - not exists mysql select stuid, stdname from student where exists (select * from course where not exists (select * from choosed where stuidstudent.stuid and cidcourse.cid)); ---------------- | stuid | stdname | ---------------- | 2 | Jessy | | 4 | Grace | | 5 | Candise | | 7 | Tracy | | 8 | Sun | | 9 | Kevin | | 10 | Richard | ---------------- 7 rows in set (0.00 sec) 4)所有选了全部课程的人的姓名 not exists - not exists mysql select stuid, stdname from student where not exists (select * from course where not exists (select * from choosed where stuidstudent.stuid and cidcourse.cid)); ---------------- | stuid | stdname | ---------------- | 1 | Bob | | 3 | Ivy | | 6 | Angela | ---------------- 3 rows in set (0.00 sec) 1)首先分析一下简单的情况双exists嵌套 mysql select stuid, stdname from student where exists (select * from course where exists (select * from choosed where stuidstudent.stuid and cidcourse.cid)); i.所有被选过的课程的数据集 select * from course where exists(select * from choosed where cidcourse.cid); ii.所有被某位学号为 stuid 的学生选过的课程的记录集stuid学生的已选课程 select * from course where exists(select * from choosed where stuidstuid and cidcourse.cid); 请注意多出了学号的筛选即stuidstuid iii.遍历每一个主查询的学号每一个学号都按第二筛选方法筛选出已选课的学生的学号。 请注意用主查询中的 student.stuid 代替了stuid。 select stuid, stdname from student where exists(
(1) select * from course where exists(
(2) select * from choosed where stuidstudent.stuid and cidcourse.cid)) (3) 这已经是整个查询语句可以看出子查询中用student.stuid替换了第2步中的 stuid没忘了吧第二步求的是选过了课程的某个学生。 实际上此句的功能就是针对每个student中的学生s把s和每个course中的课程c组成元组对(s,c)把(s,c)放到已选课程choosed中进行匹配如果存在记录c则说明学生s选择了某门课程c 于是对这个确定的学生s结果集(select * from choosed...)会返回一条包含元组(s,c)的记录从而(2)处的exists会返回真 则对这个确定的学生s结果集(select * from course...)会因为(2)处的where为真而返回这个学生s所选择的所有课程如果课程不为空的话(1)处的exists返回真否则为假。 当(1)处的exists为真时最后的结果集(select student.stdname from student...)中就应该出现学生s的记录代表这个学生选择了至少一门课。 于是根据这个分析可以把这个双exists嵌套的语句改写成如下方式 mysql select distinct stuid, stdname from student, course where (student.stuid, course.cid) in (select stuid, cid from choosed); ---------------- | stuid | stdname | ---------------- | 1 | Bob | | 2 | Jessy | | 3 | Ivy | | 4 | Grace | | 6 | Angela | | 7 | Tracy | | 10 | Richard | ---------------- 7 rows in set (0.00 sec) 或者改成更为简单的方式 mysql select distinct student.stuid, stdname from student, choosed where student.stuidchoosed.stuid; ---------------- | stuid | stdname | ---------------- | 1 | Bob | | 2 | Jessy | | 3 | Ivy | | 4 | Grace | | 6 | Angela | | 7 | Tracy | | 10 | Richard | ---------------- 7 rows in set (0.00 sec) 或者改成使用自然连接的方式 mysql select distinct stuid, stdname from student natural join choosed; ---------------- | stuid | stdname | ---------------- | 1 | Bob | | 2 | Jessy | | 3 | Ivy | | 4 | Grace | | 6 | Angela | | 7 | Tracy | | 10 | Richard | ---------------- 7 rows in set (0.00 sec) 2)再分析一下比较复杂的情况双 not exists 嵌套 select stuid, stdname from student where not exists (select * from course where not exists (select * from choosed where stuidstudent.stuid and cidcourse.cid)); i.所有没被选过的课程的数据集 select * from course where not exists(select * from choosed where cidcourse.cid); ii.所有没被某位学号为 stuid 的学生选过的课程的记录集stuid学生的未选课程 select * from course where not exists(select * from choosed where stuidstuid and cidcourse.cid); 请注意多出了学号的筛选即stuidstuid iii.遍历每一个主查询的学号每一个学号都按第二筛选方法筛选出已选全部课的学生的学号。 请注意用主查询中的 student.stuid 代替了stuid。 select stuid, stdname from student where not exists (
(1) select * from course where not exists ( (2) select * from choosed where stuidstudent.stuid and cidcourse.cid));
(3) 这已经是整个查询语句可以看出子查询中用student.stuid替换了第2步中的 stuid没忘了吧第二步求的是没有选修全部课程的某个学生。 实际上此句的功能就是针对每个student中的学生s把s和每个course中的课程c组成元组对(s,c)把(s,c)放到已选课程choosed中进行匹配如果不存在记录c则说明学生s没有选择某门课程c’。 于是对这个确定的学生s结果集(select * from choosed...)中不会存在一条包含元组(s,c’)的记录即(s,c)使得(2)处的not exists会返回真从而该课程会出现在(select * from course...) 的结果集中表示该学生s没有选择该门课程c否则若(2)处的not exists返回假则说明学生s选择了该课程c。 针对某个确定的学生s对所有的课程c进行操作(2)根据(select * from course...)的结果集以判断那些课程是学生s没选的。如果(select * from course...)的结果集为空则说明该学生s没有任何 一门课程没选即他选择了所有课程。同时(1)处的not exists会返回真(select stuid, stdname from student)也会因为(1)处的not exists为真而把该学生s放入其结果集中代表该学生选择了所有课程。 于是对这个确定的学生s结果集(select * from course...)会因为(2)处where的结果而返回这个学生s没选择的所有课程如果课程不为空的话(1)处的not exists返回假否则为真“真”即代表学生s选择了所有课程。 最后再根据上述分析对所有学生进行如上操作从而得出所有选择了所有课程的学生。 注1针对此问题双 not exists 嵌套我还想出了下面的方式但该方式不具有一般性不过可以根据不同情况加以适当的修改使其能用在别的地方。 该方法是先对choosed表中的数据按stuid分组计算出每组所含有的数据数量Ci(i1,2...)然后再计算出course中数据的数量C找出那些满足CiC的元组的stuid然后根据这些stuid找出所有学生信息。 mysql
select distinct * from student where stuid in (select distinct stuid from (select stuid, count(*) as amount from choosed group by stuid) as result where amount(select count(*) from course)); 实际上还可以根据sql中exist和not exist的使用运用逻辑的方式得出上述四种情况分别代表的含义 exists - exists 所有选了课至少一节的人的姓名 vs
not exists - exists 所有没选课一节课都没选的人的姓名 exists - not exists 所有没选全部课程的人的姓名
vs
not exists - not exists 所有选了全部课程的人的姓名 更进一步根据2)中的分析可以看出来实际上关系代数中的除法可以用此法(双not exists嵌套)解决原因如下 首先看一下双not exists嵌套的sql语句 select * from student where not exists ( (1) select * from course where not exists ( (2) select * from choosed where stuidstudent.stuid and cidcourse.cid));
(3) 实际上该sql语句做的就是用表choosed第3个select语句中指定的表中的某些属性集A第3个select语句中where后指定的属性做被除数 用表courese第2个select语句中指定的表中的某些属性集B做除数这个属性集应该是A的真子集。 需要得到的结果是表student第1个select语句中指定的表中的某些属性集C。 即 A ÷ B C 实际上就是要求出A中的某些实例这些实例要满足条件他们都所有干了B中的所有事情而且已知对应的结果存在C中反过来求A中满足条件的这些实例。 于是我们可以把上面的问题转化为一个除法问题即已知 a)选课信息表choosed其中给出了所有已选课程(cid)和选择该课的学生的学号(stuid)的对应关系 b)课程信息表course c)学生信息表student 需要求出所有选了所有课程的学生的信息。 此时恰好可以用双not exists嵌套解决sql就是上面的4)号语句。 说起来可能还是比较抽象下面给出一个不是那么直观的但仍可以使用此法解决的例子2 example 2参考http://www.blogjava.net/decode360/archive/2009/06/12/282804.html用不同于该文中的另一种方式说明解决方案 注2此问题就不能用example 1中的注1的方法解决。 已知关系模式tt(personevent) tt表中person代表人员编号event代表事件编号。要求出tt表中所有包含人员2所对应的所有事件的人员编号。 mysql select * from tt; --------------- | person | event | --------------- | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 2 | | 2 | 3 | | 4 | 1 | | 4 | 2 | | 4 | 3 | | 5 | 1 | | 5 | 6 | | 6 | 1 | --------------- 11 rows in set (0.00 sec) 实际上可以把该问题中涉及的表分成如下三个 a)人员和事件的对应关系表tt为了避免重名将之改名为tt3 b)人员2所对应的所有事件表tt2 由select event from tt where person2而得将之命名为tt2 mysql select event from tt where person2; ------- | event | ------- | 2 | | 3 | ------- 2 rows in set (0.00 sec) c)包含所有人员的表tt1由select distinct person from tt而得将之命名为tt1 mysql select distinct person from tt; -------- | person | -------- | 1 | | 2 | | 4 | | 5 | | 6 | -------- 5 rows in set (0.00 sec) 于是问题可以变成求 tt3 ÷ tt2 tt1 (tt1包含于tt1)下面给出了3个解决此问题的sql语句实际上都差不多只是在后两个中加入了as命名将问题修改成更容易直观看出来的除法问题了。 尤其在 method 3 中实际上所执行的sql语句就是 select * from tt1 where not exists ( select * from tt2 where not exists ( select * from tt3 where tt3.eventtt2.event and tt3.persontt1.person)); 正是上面分析的除法的形式。 method 1: mysql select distinct person from tt tt1 where not exists (select * from tt tt2 where person2 and not exists (select * from tt tt3 where tt3.persontt1.person and tt3.eventtt2.event)); -------- | person | -------- | 1 | | 2 | | 4 | -------- 3 rows in set (0.00 sec) method 2: mysql select distinct person from tt tt1 where not exists (select * from (select event from tt where person2) as tt2 where not exists (select * from tt tt3 where tt3.eventtt2.event and tt3.persontt1.person)); -------- | person | -------- | 1 | | 2 | | 4 | -------- 3 rows in set (0.00 sec) method 3: mysql select * from (select distinct person from tt) as tt1 where not exists (select * from (select event from tt where person2) as tt2 where not exists (select * from tt tt3 where tt3.eventtt2.event and tt3.persontt1.person)); -------- | person | -------- | 1 | | 2 | | 4 | -------- 3 rows in set (0.00 sec) example 3: 关系模式 tt1(姓名name年龄age性别gender) tt2(账户号account_id资产asset支行branch) tt3(姓名name账户号account_id)代表姓名为name的客户拥有账户account_id mysql select * from tt1; -------------------- | name | age | gender | -------------------- | Bob | 30 | M | | Jack | 48 | M | | Ivy | 27 | F | | Sun | 19 | F | | Alex | 52 | M | -------------------- 5 rows in set (0.00 sec) mysql select * from tt2; --------------------------- | account_id | asset | branch | --------------------------- | A1 | 520 | BJ | | A2 | 180 | TJ | | A3 | 34 | SH | | A4 | 178 | GZ | | A5 | 10 | NJ | | A6 | 101 | TJ | | A7 | 19 | TJ | | A8 | 32 | BJ | | A9 | 290 | GZ | | A10 | 111 | NJ | --------------------------- 10 rows in set (0.00 sec) mysql select * from tt3; ------------------ | name | account_id | ------------------ | Bob | A1 | | Bob | A2 | | Bob | A3 | | Bob | A4 | | Bob | A5 | | Bob | A6 | | Bob | A7 | | Bob | A8 | | Bob | A9 | | Bob | A10 | | Ivy | A7 | ------------------ 11 rows in set (0.00 sec) mysql select distinct tt1.* from tt1,tt2 where (tt1.name, tt2.account_id) in (select * from tt3); -------------------- | name | age | gender | -------------------- | Bob | 30 | M | | Ivy | 27 | F | -------------------- 2 rows in set (0.00 sec) 所有开了账户的人的资料 exists - exists mysql select distinct * from tt1 where exists (select * from tt2 where exists (select * from tt3 where tt3.namett1.name and tt3.account_idtt2.account_id)); -------------------- | name | age | gender | -------------------- | Bob | 30 | M | | Ivy | 27 | F | -------------------- 2 rows in set (0.00 sec) 所有没开账户的人的资料 not exists - exists mysql select distinct * from tt1 where not exists (select * from tt2 where exists (select * from tt3 where tt3.namett1.name and tt3.account_idtt2.account_id)); -------------------- | name | age | gender | -------------------- | Jack | 48 | M | | Sun | 19 | F | | Alex | 52 | M | -------------------- 3 rows in set (0.00 sec) 所有没开所有账户的人的资料 exists - not exists mysql select distinct * from tt1 where exists (select * from tt2 where not exists (select * from tt3 where tt3.namett1.name and tt3.account_idtt2.account_id)); -------------------- | name | age | gender | -------------------- | Jack | 48 | M | | Ivy | 27 | F | | Sun | 19 | F | | Alex | 52 | M | -------------------- 4 rows in set (0.00 sec) 所有开了所有账户的人的资料 not exists - not exists mysql select distinct * from tt1 where not exists (select * from tt2 where not exists (select * from tt3 where tt3.namett1.name and tt3.account_idtt2.account_id)); -------------------- | name | age | gender | -------------------- | Bob | 30 | M | -------------------- 1 row in set (0.00 sec) 有两个问题 1)什么时候采用这种嵌套方式如何能看出某问题可以采用这种方式解决 2)有没有其他的替代方法 总结一下关于(not) exist的四种嵌套方式 exists - exists这种方式所解决的问题其实可以用其他很多方式完成而且往往遇到此类问题时更容易想到的并不是双exists嵌套而是类似于join的方式可参考example 1中1)的分析。 not exists - exists由于它与exists - exists所得结果是互为补集所以可以用全集合exists - exists的结果做差即可得到结果。 not exists - not exists这个是求关系代数中的除法最常用的方法分析过程可参考example 2所以遇到需要做除法的问题时可以用此not exists嵌套的方式解决。 not exists - exists由于它与not exists - not exists所得结果是互为补集所以可以用全集合not exists - not exists的结果做差即可得到结果。 其实最简单的就是记住这四种方式所能求得的结果的含义即 exists - exists 所有干了某事干了至少一件事的某物 vs
not exists - exists 所有没干某事一件事都没干的某物 exists - not exists 所有没干全部事情的某物 vs
not exists - not exists 所有干了全部事情的某物 一个比较简单的问题将表t中数据按id分组并求出每组中距离当前时间最近的所有数据。 参考http://www.cnblogs.com/mytechblog/articles/2105785.html 原文中给出的方法不太直观个人选择了另一种方法列举如下比较简单不加以说明了 mysql select * from t; ---------------------------------------------- | id | name | count | date | ---------------------------------------------- | 1 | apple | 10 | 2011-07-01 00:00:00 | | 1 | orange | 20 | 2011-07-02 00:00:00 | | 1 | banana | 15 | 2011-07-03 00:00:00 | | 2 | white vege | 12 | 2011-07-01 00:00:00 | | 2 | blue vege | 19 | 2011-07-02 00:00:00 | | 2 | red vege | 18 | 2011-07-02 00:00:00 | ---------------------------------------------- 6 rows in set (0.00 sec) mehtod 1: mysql select * from t where (id, date) in (select id, max(date) from t group by id); --------------------------------------------- | id | name | count | date | --------------------------------------------- | 1 | banana | 15 | 2011-07-03 00:00:00 | | 2 | blue vege | 19 | 2011-07-02 00:00:00 | | 2 | red vege | 18 | 2011-07-02 00:00:00 | --------------------------------------------- 3 rows in set (0.00 sec) method 2: mysql select * from t a where (not exists (select * from t where ida.id and datea.date)); --------------------------------------------- | id | name | count | date | --------------------------------------------- | 1 | banana | 15 | 2011-07-03 00:00:00 | | 2 | blue vege | 19 | 2011-07-02 00:00:00 | | 2 | red vege | 18 | 2011-07-02 00:00:00 | --------------------------------------------- 3 rows in set (0.00 sec)
