exists，not exists用法

exists：强调是否返回结果集，不要求知道返回什么， 如：select * from student where sex = 'm' and mark exists(select 1 from grade where ...) ，只要exists引导的子句有结果集返回，那么exists条件就算成立。注：返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，该数字没有实际意义。

exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，如：select name from student where sex = 'm' and mark in (select a,b,c from grade where ...)  ，in子句返回了a、b、c三个字段，不可以。in只允许有一个字段返回，但exists子句允许多字段返回。

exists （sql 返回结果集为真）  not exists (sql 不返回结果集为真）

举例：

表A：

     ID NAME  
                1   A1
                2   A2
                3   A3

表B：

     ID AID NAME
                1   1     B1
                2   2     B2  
                3   2     B3

表A、B关系为一对多： A.ID => B.AID

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)

执行结果：1 A1、2 A2

 

原因：

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)

--->SELECT * FROM B WHERE B.AID=１ 有值，返回真

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)

--->SELECT * FROM B WHERE B.AID=２ 有值，返回真

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)

--->SELECT * FROM B WHERE B.AID=３ 无值，返回假

 

NOT EXISTS 就是反过来

SELECT ID,NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)

结果：

3 A3

====================================实际业务场景举例========================================

业务场景：项目每半小时统计一次各个数据库表的数据总量，存入data_stat_his历史表

num：某数据资源表统计时的数据总量；

increase_num：某数据资源表统计时相较上次统计的数据增量

 

现有如下需求：为最近30天的数据总量和增量生成折线图

对于增量，只需按创建时间截取到天group by、sum(increase_num)即可；但对于总量，不能单纯的相加，因为每次统计都是在之前的基数上加减，单纯相加会导致基数不停的累加，所以需要统计出【同一天，各个数据资源 分别的max(create_time)对应数据的num总和】

SELECT
   SUM(s.num) AS num,
   date_format(s.create_time, '%Y-%m-%d') AS date
FROM
   (
      SELECT
         num,
         create_time
      FROM
         data_stat_his AS dsh
      WHERE
      NOT EXISTS (
         SELECT
            1
         FROM
            data_stat_his
         WHERE
            data_res_id = dsh.data_res_id
            AND date_format(create_time, '%Y-%m-%d') = date_format(dsh.create_time, '%Y-%m-%d')
            AND create_time > dsh.create_time
      )
   ) s
GROUP BY
   date_format(s.create_time, '%Y-%m-%d')
ORDER BY
   s.create_time DESC

使用该表与自身比较，保证数据资源id相同、create_time年月日相同的情况下，如果不存在比某一create_time更大的时间，则该条即为某一数据资源当天最晚插入的值