ITPub博客

首页 > 数据库 > MySQL > mysql数据去重和排序

mysql数据去重和排序

原创 MySQL 作者:to_be_dba 时间:2015-10-19 20:08:20 0 删除 编辑

mysql中的字母默认不区分大小写。

 

比如:

mysql> select * from (select 'a' col1 union select 'A' union select 'B'  union s

elect 'b') a order by col1 ;

+------+

| col1 |

+------+

| a    |

| B    |

+------+

2 rows in set (0.00 sec)

使用union进行去重操作,A和a看成相同数据,order by时按照字典顺序进行排序。

 

mysql> select * from (select 'a' col1 union all select 'A' union all select 'B'

 union all select 'b') a order by col1 ;

+------+

| col1 |

+------+

| a    |

| A    |

| B    |

| b    |

+------+

4 rows in set (0.00 sec)

 

 

可以通过binary关键字指定其按照二进制顺序排序:

mysql> select * from (select 'a' col1 union select 'A' union select 'B'  union s

elect 'b') a order by binary col1 ;

+------+

| col1 |

+------+

| B    |

| a    |

+------+

2 rows in set (0.05 sec)

 

 

mysql> select * from (select 'a' col1 union all select 'A' union all select 'B'

 union all select 'b') a order by binary col1 ;

+------+

| col1 |

+------+

| A    |

| B    |

| a    |

| b    |

+------+

4 rows in set (0.00 sec)

 

 

如果实际的数据是

A

a

a

B

b

 

想要得到结果

A

a

B

b

语句为:

SELECT DISTINCT col1,ASCII(col1) desc_1,ASCII(UPPER(col1)) desc_2 FROM

(SELECT 'a' as col1

union ALL

SELECT 'A' as col1

union ALL

SELECT 'a' as col1

union ALL

SELECT 'b' as col1

union ALL

SELECT 'B' as col1) a

ORDER BY desc_2,desc_1

 

以上是通过语句进行限制的,如果经常进行类似的查询(区分大小写),需要在建表时指定字段属性,如:

mysql> CREATE TABLE T( A VARCHAR(10) BINARY );

Query OK, 0 rows affected (0.36 sec)

 

mysql> insert into t values ('A');insert into t values ('a');insert into t values ('a');insert into t values ('B');insert into t values ('b');

Query OK, 1 row affected (0.11 sec)

 

Query OK, 1 row affected (0.15 sec)

 

Query OK, 1 row affected (0.17 sec)

 

Query OK, 1 row affected (0.04 sec)

 

Query OK, 1 row affected (0.04 sec)

 

mysql> select distinct a from t;

+------+

| a    |

+------+

| A    |

| a    |

| B    |

| b    |

+------+

4 rows in set (0.00 sec)

 

mysql> select distinct a from t order by a;

+------+

| a    |

+------+

| A    |

| B    |

| a    |

| b    |

+------+

4 rows in set (0.00 sec)

 

mysql> select distinct a from t order by binary a;

+------+

| a    |

+------+

| A    |

| B    |

| a    |

| b    |

+------+

4 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-1815030/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    392793