group by,sum(),having count(*),case when探究

case when学习：
mysql> create table employee(name varchar(20),birthday int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into employee values ('liys',1989);
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee values ('zhangs',1956);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee values ('wangs',1999);
Query OK, 1 row affected (0.00 sec)

mysql> select name,birthday,
    ->  case
    ->         when birthday<'1981' then 'old'
    ->         when birthday>'1990' then 'yong'
    ->         else 'ok' END YORN
    -> from employee;
+--------+----------+------+
| name   | birthday | YORN |
+--------+----------+------+
| liys   |     1989 | ok   |
| zhangs |     1956 | old  |
| wangs  |     1999 | yong |
+--------+----------+------+
3 rows in set (0.00 sec)
 

group by,sum(),having count(*)学习：
mysql> select * from employee;
+--------+----------+----------+
| name   | province | birthday |
+--------+----------+----------+
| liys   | beijing  | 1989     |
| liss   | beijing  | 1999     |
| zhnags | beijing  | 2000     |
| zss    | hebei    | 1978     |
| zssss  | hebei    | 1990     |
| zsaf   | hebei    | 2000     |
| zsaf   | sichuan  | 1945     |
| wag    | sichuan  | 1999     |
+--------+----------+----------+
8 rows in set (0.00 sec)

mysql> select province,sum(birthday) from employee group by province;
+----------+---------------+
| province | sum(birthday) |
+----------+---------------+
| beijing  |          5988 |
| hebei    |          5968 |
| sichuan  |          3944 |
+----------+---------------+
3 rows in set (0.00 sec)

mysql> select province,count(*) from employee group by province;
+----------+----------+
| province | count(*) |
+----------+----------+
| beijing  |        3 |
| hebei    |        3 |
| sichuan  |        2 |
+----------+----------+
3 rows in set (0.00 sec)

mysql> select province,count(*) from employee group by province having count(*)=2;
+----------+----------+
| province | count(*) |
+----------+----------+
| sichuan  |        2 |
+----------+----------+
1 row in set (0.01 sec)

mysql> select province,sum( case when birthday > 1999 then 1 else 0 end) from employee group by province;
+----------+---------------------------------------------------+
| province | sum( case when birthday > 1999 then 1 else 0 end) |
+----------+---------------------------------------------------+
| beijing  |                                                 1 |
| hebei    |                                                 1 |
| sichuan  |                                                 0 |
+----------+---------------------------------------------------+
3 rows in set (0.00 sec)

下面这个sql having count(*)= sum 实际是把count（*）中的所有数据又都过滤了一遍，组里面所有数据满足条件才会显出分组
mysql> select province,count(*) from employee group by province having count(*)= sum( case when birthday > 1960 then 1 else 0 end);
+----------+----------+
| province | count(*) |
+----------+----------+
| beijing  |        3 |
| hebei    |        3 |
+----------+----------+
2 rows in set (0.00 sec)

这种情况下往往需要把having count(*)= sum（）里的过滤条件写到where里，因为having count(*)= sum（）里面的sql不会走索引
mysql> select province,count(*) from employee where birthday > 1960 group by province;
+----------+----------+
| province | count(*) |
+----------+----------+
| beijing  |        3 |
| hebei    |        3 |
| sichuan  |        1 |
+----------+----------+
3 rows in set (0.00 sec)

那如何优化这个sql呢？
注意事项：having里面的逻辑是：分组后，同一组里面的每条记录都需要满足case when子句里面的条件，否则就丢弃。sql改写是要注意满足这个逻辑，否则会出现改写后的数据比原始sql执行的结果集要多

那就要用到exits这个功能了。
SQL中的(NOT) EXISTS是一种条件运算符，用于检查一个子查询是否返回结果，就是把where的结果集再筛选一遍，exist存在的就是说这一行的某个列值在另一个表里存在。
反之 not exits就是不存在另一个表里，把不存在的保留下来。

EXISTS：如果子查询返回至少一行结果，则返回true。它可以与WHERE子句一起使用，以根据子查询的结果过滤数据。
NOT EXISTS：如果子查询不返回任何结果，则返回true。它可以与WHERE子句一起使用，以排除具有特定条件的数据。
这两个运算符可以与SELECT、DELETE、UPDATE语句一起使用，以根据子查询返回的结果来决定要执行的操作。

mysql> select * from employee_b;
+------+
| name |
+------+
| liys |
| liss |
+------+
2 rows in set (0.00 sec)

mysql> select * from employee;
+--------+----------+----------+
| name   | province | birthday |
+--------+----------+----------+
| liys   | beijing  | 1989     |
| liss   | beijing  | 1999     |
| zhnags | beijing  | 2000     |
| zss    | hebei    | 1978     |
| zssss  | hebei    | 1990     |
| zsaf   | hebei    | 2000     |
| zsaf   | sichuan  | 1945     |
| wag    | sichuan  | 1999     |
+--------+----------+----------+
8 rows in set (0.00 sec)

mysql> select * from employee where province='beijing';
+--------+----------+----------+
| name   | province | birthday |
+--------+----------+----------+
| liys   | beijing  | 1989     |
| liss   | beijing  | 1999     |
| zhnags | beijing  | 2000     |
+--------+----------+----------+
3 rows in set (0.00 sec)

mysql> select * from employee where province='beijing' and  exists (select 1 from employee_b where employee_b.name=employee.name);
+------+----------+----------+
| name | province | birthday |
+------+----------+----------+
| liys | beijing  | 1989     |
| liss | beijing  | 1999     |
+------+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from employee where province='beijing' and  not exists (select 1 from employee_b where employee_b.name=employee.name);
+--------+----------+----------+
| name   | province | birthday |
+--------+----------+----------+
| zhnags | beijing  | 2000     |
+--------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from employee where  not ( birthday > 1960);
+------+----------+----------+
| name | province | birthday |
+------+----------+----------+
| zsaf | sichuan  | 1945     |
+------+----------+----------+
1 row in set (0.00 sec)

通过下面这个sql,就把province=sichuan的都过滤掉了

mysql> select * from employee a where birthday >1960 and not exists (select 1 from employee b where a.province=b.province and not (b.birthday > 1960));
+--------+----------+----------+
| name   | province | birthday |
+--------+----------+----------+
| liys   | beijing  | 1989     |
| liss   | beijing  | 1999     |
| zhnags | beijing  | 2000     |
| zss    | hebei    | 1978     |
| zssss  | hebei    | 1990     |
| zsaf   | hebei    | 2000     |
+--------+----------+----------+
6 rows in set (0.00 sec)

mysql>