MySQL 多重排序依据(嵌套的种类) [英] MySQL multiple order by (kind of nested)

问题描述

一个奇怪的问题，它正在工作，但我不明白为什么.我希望了解为什么这是有效的.我已经在网络和 stackOverflow 上搜索了答案，但找不到(我通过反复试验得出了这个代码......)

我一直在尝试按 3 列排序.

我希望将前 2 列的结果混合在一起，但首先是按第三列(日期)排序的所有结果

这是基本的数据库表结构:

• HomePage = 布尔值，
• FeaturedProfile = 布尔值
• 名称 = 字符串
• DateModified = 日期

注意:HomePage 和 FeaturedProfile 都可以是 0(所以任何结果都是 0 应该低于(并且是！))

这是有效的 SQL 代码:

SELECT DISTINCT主页、个人资料、姓名从 tProfilesORDER BY (HomePage = 0 AND FeaturedProfile = 0), e.DateModified DESC

这是我不明白的 ORDER BY，因为我的逻辑会说使用 1 而不是 0 + 我不确定括号在做什么，因为我认为下面会起作用(就像在 PHP 中一样)SUM)，对我来说似乎更合乎逻辑.

ORDER BY (HomePage, FeaturedProfile), e.DateModified DESC

希望这个问题被允许，因为它一直困扰着我，为什么这会起作用，我想学习这个以供将来参考....

提前致谢，我很乐意为任何可以帮助我理解这一点的人打分！

解决方案

ORDER BY 子句中的第一个表达式，即:

(e.HomePage = 0 AND e.FeaturedProfile = 0)

是 MySQL 的简写，相当于 ANSI 标准:

 CASE WHEN (e.HomePage = 0 AND e.FeaturedProfile = 0) THEN 1WHEN(e.HomePage 不为 NULL 且 e.FeaturedProfile 不为 NULL) THEN 0否则为空结尾

该表达式后面省略了 ASC/DESC 关键字，因此它默认为 ASC(升序).

最终结果是将按以下顺序返回行:

第一:布尔表达式返回 0 (FALSE) 的行(即，任何行的两列都具有非 NULL 值，并且其中一(或两)列具有非零值.)

后跟:布尔表达式返回 1 (TRUE) 的行(即两列都具有零值的任何行)

后跟其中任一列为 NULL 的行.

<小时>

您说得对，还有其他方法可以实现相同的结果.例如:

ORDER BY (e.HomePage AND e.FeaturedProfile), e.DateModified DESC

ORDER BY 子句中的第一个表达式被计算为布尔表达式.这是说同样的事情:如果任一列为 NULL，则结果为 NULL.否则，如果任一列为 FALSE(具有零值)，则结果为 FALSE (0).否则结果将为 TRUE (1).该表达式的结果按升序排序.

如果以这种方式处理 NULL 值并不重要，如果我们真正感兴趣的是获取 HomePage 或 FeaturedProfile 为非零的行:

ORDER BY (e.HomePage OR e.FeaturedProfile) DESC, e.DateModified DESC

这略有不同.例如，如果 HomePage 为 1 且 FeaturedProfile 为 NULL，则表达式的计算结果为 1，并且该行将首先排序.(在这种情况下，原始将返回 NULL，并且该行将排在最后.)

A strange problem, in that it is working but i dont understand why. I am hoping to learn why this is working. I have searched the net and stackOverflow for an answer but can not find one (i came out with this code through trial and error...)

I have been trying to order by 3 columns.

I wanted the results from the first 2 columns mixed together but first and then all results ordered by the third column (a date)

This is the basic database table structure:

• HomePage = boolean,
• FeaturedProfile = boolean
• Name = string
• DateModified = date

NOTE: HomePage and FeaturedProfile can both be 0 (so any results that are both 0 should be below (and are!))

This is the SQL code that works:

SELECT DISTINCT
HomePage, Profile, Name
FROM tProfiles
ORDER BY (HomePage = 0 AND FeaturedProfile = 0), e.DateModified DESC

it is the ORDER BY that i dont understand as my logic would say use a 1 and not a 0 + i am not to sure what the brackets are doing either as i would assume the below would have worked (like in a PHP SUM) and seems more logical to me.

ORDER BY (HomePage, FeaturedProfile), e.DateModified DESC

Hope this questions is allowed as it is bugging me as to why this is working and i would like to learn this for future reference....

Thanks in advance, and i will be delighted to up-mark anyone that can help me understand this!

解决方案

The first expression in your ORDER BY clause, i.e:

(e.HomePage = 0 AND e.FeaturedProfile = 0)

Is MySQL shorthand, it's equivalent to the ANSI-standard:

 CASE WHEN (e.HomePage = 0 AND e.FeaturedProfile = 0) THEN 1
      WHEN (e.HomePage IS NOT NULL AND e.FeaturedProfile IS NOT NULL) THEN 0
      ELSE NULL
 END

The ASC/DESC keyword is omitted following that expression, so it defaults to ASC (ascending sequence).

The net result is that rows will be returned in this order:

First: rows where the boolean expression returns 0 (FALSE) (that is, any row that has non-NULL values for both of the columns, and a non-zero value for either (or both) columns.)

Followed by: rows where the boolean expression returns 1 (TRUE) (i.e. any row that has zero values for both of the columns)

Followed by rows where either of the columns is NULL.

---

You are right that there are other ways to achieve an equivalent result. For example:

ORDER BY (e.HomePage AND e.FeaturedProfile), e.DateModified DESC

The first expression in the ORDER BY clause is evaluated as boolean expression. That's saying the same thing: if either column is NULL, the result is NULL. Else if either column is FALSE (has a zero value), the result is FALSE (0). Else the result will be TRUE (1). The result of that expression is sorted in ascending order.

If handling NULL values in that exact way isn't important, if what we're really interested in is getting rows that have HomePage or FeaturedProfile as non-zero:

ORDER BY (e.HomePage OR e.FeaturedProfile) DESC, e.DateModified DESC

This is slightly different. If, for example, HomePage is 1 and FeaturedProfile is NULL, the expression will evaluate to 1, and the row will be sorted first. (The original would return NULL in this case, and the row would be sorted last.)

这篇关于MySQL 多重排序依据(嵌套的种类)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！