有条件的MySQL按两个（同等重要）列排序 [英] Conditional MySQL order by two (equally important) columns

问题描述

我有一个包含很多列的产品列表，但是对我的问题而言，最重要的三个是这些（带有一些随机数据）：

  | -------------------------------------- | 
 |标题| category_id |日期| 
 | ---------- | ------------- || ------------ | 
 |产品1 | 1 | 2012-04-18 | 
 |产品2 | 1 |注册| 
 |产品3 | 17 |注册| 
 |产品4 | 17 | 2012-04-10 | 
 |产品5 | 17 | 2012-04-20 | 
 |产品6 | 1 |注册| 
 |产品7 | 2 | 2012-04-20 | 
 |产品8 | 2 |注册| 
 |产品9 | 17 | 2012-04-16 | 
 |产品10 | 22 | 2011-11-26 | 
 |产品11 | 22 | 2011-12-25 | 
 |产品12 | 30 | 2012-04-01 | 
 |产品13 | 2 | 2011-12-31 | 
 |产品14 | 30 | 2010-05-06 | 
 | -------------------------------------- | 
  

具有相同 category_id 的产品应为依次列出（此时甚至可以通过 ORDER BY category_id解决），但我还必须注意 date 列： category_id 和类别中的产品必须按 date 降序（ category_id ，顶部是最新产品，依此类推），因此理想情况下结果集应该是这样的（在类别 groups之间添加了换行符，以便更加透明）：

  | ----------------------------------- --- | 
 |标题| category_id |日期| 
 | ---------- | ------------- || ------------ | 
 |产品5 | 17 | 2012-04-20 | 
 |产品9 | 17 | 2012-04-16 | 
 |产品4 | 17 | 2012-04-10 | 
 |产品3 | 17 |注册| 
 
 |产品7 | 2 | 2012-04-20 | 
 |产品13 | 2 | 2011-12-31 | 
 |产品8 | 2 |注册| 
 
 |产品1 | 1 | 2012-04-18 | 
 |产品2 | 1 |注册| 
 |产品6 | 1 |注册| 
 
 |产品12 | 30 | 2012-04-01 | 
 |产品14 | 30 | 2010-05-06 | 
 
 |产品11 | 22 | 2011-12-25 | 
 |产品10 | 22 | 2011-11-26 | 
 | -------------------------------------- | 
  

是否可以仅通过一个查询获得该结果集，什么是可行的解决方案？ / p>

在此先感谢
marcell

解决方案

您需要要在子查询中查找每个类别的最新日期，请将此子查询加入表并按3个字段排序：

  SELECT p。 * FROM产品p 
 JOIN 
（选择category_id，MAX（date）作为category_date FROM产品
 GROUP BY category_id）pg 
 ON p.category_id = pg.category_id 
 ORDER BY pg.category_date DESC，p.category_id，p.date DESC 
  

I have a list of products with lots of columns but the 3 most important for my problem are these (with some random data):

|--------------------------------------|
| title     | category_id | date       |
| ----------|-------------|------------|
|  Product1 |           1 | 2012-04-18 |
|  Product2 |           1 | 0000-00-00 |
|  Product3 |          17 | 0000-00-00 |
|  Product4 |          17 | 2012-04-10 |
|  Product5 |          17 | 2012-04-20 |
|  Product6 |           1 | 0000-00-00 |
|  Product7 |           2 | 2012-04-20 |
|  Product8 |           2 | 0000-00-00 |
|  Product9 |          17 | 2012-04-16 |
| Product10 |          22 | 2011-11-26 |
| Product11 |          22 | 2011-12-25 |
| Product12 |          30 | 2012-04-01 |
| Product13 |           2 | 2011-12-31 |
| Product14 |          30 | 2010-05-06 |
|--------------------------------------|

The products with the same category_id should be listed one after another (at this point this could be even solved by "ORDER BY category_id"), BUT I have to take care about the date column as well: the category_id and the products within the categories have to be sorted by date descending (the category_id with the newest product on top and so on), so ideally the resultset should be something like this (added line breaks between category "groups" just to be more transparent):

|--------------------------------------|
| title     | category_id | date       |
| ----------|-------------|------------|
|  Product5 |          17 | 2012-04-20 |
|  Product9 |          17 | 2012-04-16 |
|  Product4 |          17 | 2012-04-10 |
|  Product3 |          17 | 0000-00-00 |

|  Product7 |           2 | 2012-04-20 |
| Product13 |           2 | 2011-12-31 |
|  Product8 |           2 | 0000-00-00 |

|  Product1 |           1 | 2012-04-18 |
|  Product2 |           1 | 0000-00-00 |
|  Product6 |           1 | 0000-00-00 |

| Product12 |          30 | 2012-04-01 |
| Product14 |          30 | 2010-05-06 |

| Product11 |          22 | 2011-12-25 |
| Product10 |          22 | 2011-11-26 |
|--------------------------------------|

Would it be possible to get this resultset with only one query and what would be a working solution?

Thanks in advance, marcell

解决方案

You need to find latest date for each category in a subquery, join this subquery to your table and order by 3 fields:

SELECT p.* FROM products p
JOIN
( SELECT category_id, MAX(date) as category_date FROM products
  GROUP BY category_id ) pg
ON p.category_id = pg.category_id
ORDER BY pg.category_date DESC, p.category_id, p.date DESC

这篇关于有条件的MySQL按两个（同等重要）列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！