如何按多列而不是按值排序? [英] How can I sort by multiple columns but not by value?

问题描述

问题标题可能有点含糊，所以请随意将其更改为更有意义的内容，我只是找不到合适的词.

Question title might be a bit vague, so feel free to change it into something that makes more sense, I just couldn't find the right words.

所以这是我的问题:

在我的表中有 2 个重要的列

In my table there are 2 important columns

• date_ added(日期时间值)
• special_price(数字)

special_price 是 NULL 或数值，具体取决于特价.

special_price is either NULL or a numeric value depending on what the special price is.

我将描述我现在想要对结果进行排序的方式:

I'll describe the way I want to order my result now:

首先，我想查看按日期排序的所有没有 special_price (NULL) 的项目(最新的在前)，然后我想查看有 special_price 的项目(所以除了 NULL) 按日期排序.

First I want to see all the items with no special_price (NULL) ordered by date (newest first), Then I want to see the items that do have a special_price (so anything other than NULL) ordered by date.

起初我试过这个:

SELECT * FROM products ORDER BY special_price, date_added DESC

这对于所有 special_price 值都在 NULL 的第一部分效果很好，但是当它开始时，确实在 special_price 列中的值上订购了 special_price 的项目.这当然是有道理的，因为在查询中我想先按 special_price 订购.

This worked great for the first part where all the special_price values where NULL, but when it started with the the items that did have a special_price ordered on the value that was in the special_price column. This makes sense of course because in the query I wanted to order by special_price first.

那么如何让查询忽略 special_price 的值而只查看它是否为 NULL 呢?所以我得到所有 NULL 按日期排序，然后我得到所有 NOT NULL 按日期排序.

So how can I let the query ignore the value of special_price and just see whether it's NULL or not? So I get all NULL's ordered by date and then I get all the NOT NULL's ordered by date.

推荐答案

ORDER BY IF( ISNULL( special_price ), 0, 1 ), date_added DESC

这篇关于如何按多列而不是按值排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！