按两列排序 MySQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/514943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order a MySQL table by two columns
提问by truppo
How do I sort a MySQL table by two columns?
如何按两列对 MySQL 表进行排序？
What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article title, then the article date)
我想要的是先按最高评分排序的文章，然后是最近的日期。例如，这将是一个示例输出（左# 是评分，然后是文章标题，然后是文章日期）
50 | This article rocks | Feb 4, 2009 35 | This article is pretty good | Feb 1, 2009 5 | This Article isn't so hot | Jan 25, 2009
The relevant SQL I'm using is:
我正在使用的相关 SQL 是：
ORDER BY article_rating, article_time DESC
I can sort by one or the other, but not both.
我可以按其中一个排序，但不能同时排序。
回答by truppo
Default sorting is ascending, you need to add the keyword DESC to both your orders:
默认排序是升序，您需要在两个订单中添加关键字 DESC：
ORDER BY article_rating DESC, article_time DESC
回答by Tomalak
ORDER BY article_rating, article_time DESC
will sort by article_time only if there are two articles with the same rating. From all I can see in your example, this is exactly what happens.
仅当有两篇具有相同评分的文章时，才会按文章时间排序。从我在你的例子中看到的一切来看，这正是发生的事情。
↓ primary sort secondary sort ↓
1. 50 | This article rocks | Feb 4, 2009 3.
2. 35 | This article is pretty good | Feb 1, 2009 2.
3. 5 | This Article isn't so hot | Jan 25, 2009 1.
but consider:
但请考虑：
↓ primary sort secondary sort ↓
1. 50 | This article rocks | Feb 2, 2009 3.
1. 50 | This article rocks, too | Feb 4, 2009 4.
2. 35 | This article is pretty good | Feb 1, 2009 2.
3. 5 | This Article isn't so hot | Jan 25, 2009 1.
回答by Learning
ORDER BY article_rating ASC , article_time DESC
DESCat the end will sort by both columns descending. You have to specify ASCif you want it otherwise
DESC最后将按两列降序排序。您必须指定ASC是否需要否则
回答by Jiri Fornous
This maybe help somebody who is looking for the way to sort table by two columns, but in paralel way. This means to combine two sorts using aggregate sorting function. It's very useful when for example retrieving articles using fulltext search and also concerning the article publish date.
这可能会帮助那些正在寻找按两列排序表格但以并行方式排序的人。这意味着使用聚合排序功能组合两种排序。例如，当使用全文搜索检索文章以及关于文章发布日期时，它非常有用。
This is only example, but if you catch the idea you can find a lot of aggregate functions to use. You can even weight the columns to prefer one over second. The function of mine takes extremes from both sorts, thus the most valued rows are on the top.
这只是一个例子，但如果你抓住了这个想法，你会发现很多聚合函数可以使用。您甚至可以对列进行加权以使其更喜欢一秒。我的函数从两种类型中取极端，因此最有价值的行在顶部。
Sorry if there exists simplier solutions to do this job, but I haven't found any.
对不起，如果有更简单的解决方案来完成这项工作，但我还没有找到任何解决方案。
SELECT
`id`,
`text`,
`date`
FROM
(
SELECT
k.`id`,
k.`text`,
k.`date`,
k.`match_order_id`,
@row := @row + 1 as `date_order_id`
FROM
(
SELECT
t.`id`,
t.`text`,
t.`date`,
@row := @row + 1 as `match_order_id`
FROM
(
SELECT
`art_id` AS `id`,
`text` AS `text`,
`date` AS `date`,
MATCH (`text`) AGAINST (:string) AS `match`
FROM int_art_fulltext
WHERE MATCH (`text`) AGAINST (:string IN BOOLEAN MODE)
LIMIT 0,101
) t,
(
SELECT @row := 0
) r
ORDER BY `match` DESC
) k,
(
SELECT @row := 0
) l
ORDER BY k.`date` DESC
) s
ORDER BY (1/`match_order_id`+1/`date_order_id`) DESC
回答by Jiri Fornous
The following will order your data depending on both column in descending order.
以下将根据两列按降序对您的数据进行排序。
ORDER BY article_rating DESC, article_time DESC
