1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需99元，即可下载本站文章涉及的文件和软件。
第一步：数据准备
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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) |
第二步：数据验证
1.distinct单字段去重
|
1
2
3
4
5
6
7
8
9
|
mysql> select distinct addr from my_student;+--------+| addr |+--------+| 北京 || 上海 || NULL |+--------+3 rows in set (0.00 sec) |
2.count和distinct配合使用
1).count(distinct col) 计算该列除 NULL 之外的不重复行数。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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) |
注意比较①和②的结果,是不同的.
2).count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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) |
注意比较③和④的结果,也是不同的.
3.上述同样的操作在oracle数据库验证
1).针对上述1)的场景在oracle数据库中验证后,count(distinct col)也是会计算该列除 NULL 之外的不重复行数,同MySQL一样
2).针对上述2)的场景,count(distinct col1, col2)的写法在oracle数据库中不支持.
