mysql数据去重和排序

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1815030/