SQL之 distinct

http://www.cnblogs.com/rainman/archive/2013/05/03/3058451.html
点上面这个链接有关于distinct的讲解，我这里只是使用mysql对其测试了一下。

#创建表b
create table b(
id int primary key auto_increment,
xing char(10) not null default ‘’,
ming char(10) not null default ''
);

所有数据:

mysql> select * from b;  
+----+------+------+
| id | xing | ming |
+----+------+------+
|  1 | rain | man  |
|  2 | rain | man  |
|  3 | rain | an   |
|  4 | rain | an   |
|  5 | rain | sb   |
+----+------+------+
5 rows in set (0.00 sec)

通过两列column结合 distinct过滤出重复记录：

mysql> select distinct xing, ming from b;
+------+------+
| xing | ming |
+------+------+
| rain | man  |
| rain | an   |
| rain | sb   |
+------+------+
3 rows in set (0.01 sec)

distinct 过滤重复记录，上面的两条重读记录过滤成了一条

去重之后的数目：

mysql> select count(distinct ming) from b;
+----------------------+
| count(distinct ming) |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.00 sec)

mysql可以两个字段 一起 作为去重条件：

mysql> select count(distinct xing, ming) from b;
+----------------------------+
| count(distinct xing, ming) |
+----------------------------+
|                          3 |
+----------------------------+

使用子查询达到上面的效果：

mysql> select count(*) from (select distinct xing, ming from b) AS M;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)