SQL SELECT ORDER BY 多列取决于其他列的值 [英] SQL SELECT ORDER BY multiple columns depending on value of other column

问题描述

我有一个包含以下列的表格:

I have a table with the following columns:

id |重温(布尔)|FL(十进制)|FR(十进制) |RL(十进制)|RR(十进制)|日期

id | revisit (bool) | FL (decimal) | FR (decimal) | RL (decimal) | RR (decimal) | date

我需要编写一个 SELECT 语句，该语句将对多个列进行 ORDER BY，具体取决于revisit"字段的值.

I need to write a SELECT statement that will ORDER BY on multiple columns, depending on the value of the 'revisit' field.

1. ORDER BY 'revisit' DESC - 此字段值为 1 的记录将在前，0 将在后
2. 如果 'revisit' = 1，则按 FL、FR、RL 和 RR 中存在的最小值排序.因此，如果记录 1 在这些字段中的值是 4.6、4.6、3.0、5.0，而记录 2 的值是 4.0、3.1、3.9 和 2.8，那么将首先返回记录 2，因为它在这四列中拥有最低值.
3. 如果 'revisit' = 0，则按日期排序 - 最早的日期将在前.

到目前为止，我只对 'revisit' 进行了正确排序，如果 'revisit' = 0，则按日期排序，但当 'revisit' = 1 时按四列同时排序.

So far I have the 'revisit' alone ordering correctly, and ordering by date if 'revisit' = 0, but ordering by the four columns simultaneously when 'revisit' = 1 does not.

SELECT *
FROM vehicle
ORDER BY
`revisit` DESC,
CASE WHEN `revisit` = 1 THEN `FL` + `FR` + `RR` + `RL` END ASC,
CASE WHEN `revisit` = 0 THEN `date` END ASC

相反，它似乎是按四列的总数排序(这在给定加法符号的情况下是有意义的)，所以我如何同时按这些列排序，作为单独的列，而不是总和.

Instead it seems to be ordering by the total of the four columns (which would make sense given addition symbols), so how do I ORDER BY these columns simultaneously, as individual columns, rather than a sum.

我希望这是有道理的，谢谢！

I hope this makes sense and thanks!

推荐答案

在您当前的查询中，您按四列的总和排序.您可以使用 least 以获得最低值，因此您的 order by 子句可能如下所示:

In your current query, you order by the sum of the four columns. You can use least to get the lowest value, so your order by clause could look like:

SELECT *
FROM vehicle
ORDER BY
  `revisit` DESC,
  CASE WHEN `revisit` = 1 THEN LEAST(`FL`, `FR`, `RR`, `RL`) END ASC,
  CASE WHEN `revisit` = 0 THEN `date` END ASC

当然，这只会按最低值排序.如果两行都共享相同的最低值，则不会对第二低的值进行排序.要做到这一点有点困难，我并没有真正从你的问题中得到你是否需要那个.

Of course this would sort only by the lowest value. If two rows would both share the same lowest value, there is no sorting on the second-lowest value. To do that is quite a bit harder, and I didn't really get from your question whether you need that.

这篇关于SQL SELECT ORDER BY 多列取决于其他列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！