MySQL中count(distinct col...)组合使用的注意要点详解

目录

• 第一步：数据准备
• 第二步：数据验证
• 1.distinct单字段去重
• 2.count和distinct配合使用
• 3.上述同样的操作在oracle数据库验证
• 总结

mysql> select * from my_student;
+----+------------+-----------+--------+--------+
| id | number     | name      | sex    | addr   |
+----+------------+-----------+--------+--------+
|  1 | itcast0001 | Jim       | female | 北京   |
|  2 | itcast0002 | HanMeimei | female | 上海   |
|  3 | itcast0003 | Kate      | female | NULL   |
|  4 | itcast0004 | Tom       | male   | NULL   |
|  5 | itcast0005 | LinTao    | male   | NULL   |
|  6 | itcast0006 | 张越      | 女     | NULL   |
+----+------------+-----------+--------+--------+
6 rows in set (0.00 sec)

mysql> select distinct addr from my_student;
+--------+
| addr   |
+--------+
| 北京   |
| 上海   |
| NULL   |
+--------+
3 rows in set (0.00 sec)

    mysql> select count(distinct addr) from my_student;                            ①
    +----------------------+
    | count(distinct addr) |
    +----------------------+
    |                    2 |
    +----------------------+
    1 row in set (0.00 sec)
    
    -- 把上述SQL改写成 select count(1) from (select distinct col from ...) a; 
    mysql> select count(1) from (select distinct addr from my_student) a;        ②
    +----------+
    | count(1) |
    +----------+
    |        3 |
    +----------+
    1 row in set (0.00 sec)

    mysql> select distinct sex,addr from my_student;
    +--------+--------+
    | sex    | addr   |
    +--------+--------+
    | female | 北京   |
    | female | 上海   |
    | female | NULL   |
    | male   | NULL   |
    | 女     | NULL   |
    +--------+--------+
    5 rows in set (0.00 sec)
    
    mysql> select count(distinct sex,addr) from my_student;                     ③
    +--------------------------+
    | count(distinct sex,addr) |
    +--------------------------+
    |                        2 |
    +--------------------------+
    1 row in set (0.00 sec)

    mysql> select count(1) from (select distinct sex,addr from my_student) a;    ④
    +----------+
    | count(1) |
    +----------+
    |        5 |
    +----------+
    1 row in set (0.00 sec)