第一步:数据准备
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单字段去重
mysql> select distinct addr from my_student;
+--------+
| addr |
+--------+
| 北京 |
| 上海 |
| NULL |
+--------+
3 rows in set (0.00 sec)
2.count和distinct配合使用
1).count(distinct col) 计算该列除 NULL 之外的不重复行数。
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。
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数据库中不支持.