mysql中的order语句_【MySql】1.2 mysql中 Order By 语句的用法

一、用法介绍

Order by语句是用来排序的，默认为升序排序(即：从1到9，从a到z)，order by desc为降序排序(即：从9到1，从z到a)

SELECT column_name(s)

FROM table_name

ORDER BY column_name DESC

例：

mysql> select * from mysql_order_by_test order by uid asc;

+-----+-------+-----+

| uid | name| sex |

+-----+-------+-----+

|1 | ZHANG |1 |

|2 | LI|2 |

|3 | WANG|1 |

+-----+-------+-----+

3 rows in set (0.00sec)

这条语句是按照uid正序查询数据，也就是按照uid从小到大排列

例：

mysql> select * from mysql_order_by_test order by uid desc;

+-----+-------+-----+

| uid | name| sex |

+-----+-------+-----+

|3 | WANG|1 |

|2 | LI|2 |

|1 | ZHANG |1 |

+-----+-------+-----+

3 rows in set (0.00sec)

MySQL Order By根据两列进行分类：

很多时候，我们需要同时根据两列内容(或者更多列)来对数据进行分类。当指定的列数多于一列时，仅在第一列的值完全相同时才参考第二列：

SELECT column_name(s)

FROM table_name

ORDER BY column_name1, column_name2

二、mysql "group by"与"order by"的研究

1.先创建一个表，并添加数据

CREATE TABLE `test` (

`id` INT(10) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(255) NOT NULL,

`category_id` INT(10) NOT NULL,

`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 默认值为当前系统时间

PRIMARY KEY (`id`)

)

ENGINE=MyISAM ROW_FORMAT=DEFAULT;

INSERT INTO `test` (`id`, `name`, `category_id`, `date`) VALUES

(1, 'aaa', 1, '2010-06-10 19:14:37'),

(2, 'bbb', 2, '2010-06-10 19:14:55'),

(3, 'ccc', 1, '2010-06-10 19:16:02'),

(4, 'ddd', 1, '2010-06-10 19:16:15'),

(5, 'eee', 2, '2010-06-10 19:16:35');

现在需要取出每个分类中最新的内容：

(1)如果select * from test group by category_id order by `date`，结果：

明显，这不是想要的数据，原因是只从分组后的每个组里取得了第一条信息，分析如下：

2.根据上面的分析，有两个解决方法：

(1)where+group by(对小组进行排序)

分析分组后的结构：idcategory_id

11

31

41

idcategory_id

22

52

对group by里的小组进行排序的函数我只查到group_concat()可以进行排序，但该函数的作

用是将小组里的字段里的值进行串联起来。

select group_concat(id order by `date` desc) from `test` group by category_id;

select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test`

group by category_id

select * from test where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id) order by date desc;

(2)从from返回的数据下手脚(即用子查询)

select * from `test` order by `date` desc

分析分组后的结构：idcategory_id

52

22

idcategory_id

41

31

11

select * from (select * from `test` order by `date` desc) `temp`

group by category_id order by `date` desc