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/，如需轉載，請註明出處，否則將追究法律責任。