MYSQL ORDER BY CASE 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4346823/
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
MYSQL ORDER BY CASE Issue
提问by ATLChris
I have a database like this:
我有一个这样的数据库：
-------------------------------------------------------------------
| id_one | id_two | timestamp_one | timestamp_two |
-------------------------------------------------------------------
| 27 | 35 | 9:30 | NULL |
-------------------------------------------------------------------
| 35 | 27 | NULL | 9:35 |
-------------------------------------------------------------------
| 27 | 35 | 9:34 | NULL |
-------------------------------------------------------------------
| 35 | 27 | NULL | 9:33 |
-------------------------------------------------------------------
I need pull all 4 rows
我需要拉所有 4 行
ORDER BY 'timestamp_one' if 'id_one'=27 or
ORDER BY 'timestamp_two' if 'id_one'=27
This is the statement I have now:
这是我现在的声明：
SELECT * FROM tablename
WHERE id_one=27 OR id_two=27
ORDER BY
CASE WHEN id_one=27 THEN timestamp_one END DESC,
CASE WHEN id_two=27 THEN timestamp_two END DESC
This works good in that is outputs this:
这很有效，因为输出如下：
-------------------------------------------------------------------
| id_one | id_two | timestamp_one | timestamp_two |
-------------------------------------------------------------------
| 27 | 35 | 9:30 | NULL |
-------------------------------------------------------------------
| 27 | 35 | 9:34 | NULL |
-------------------------------------------------------------------
| 35 | 27 | NULL | 9:33 |
-------------------------------------------------------------------
| 35 | 27 | NULL | 9:35 |
-------------------------------------------------------------------
But I need to two timestamp columns to order like they are one so it would order like this:
但是我需要两个时间戳列来排序，就像它们是一个一样，所以它会这样排序：
-------------------------------------------------------------------
| id_one | id_two | timestamp_one | timestamp_two |
-------------------------------------------------------------------
| 27 | 35 | 9:30 | NULL |
-------------------------------------------------------------------
| 35 | 27 | NULL | 9:33 |
-------------------------------------------------------------------
| 27 | 35 | 9:34 | NULL |
-------------------------------------------------------------------
| 35 | 27 | NULL | 9:35 |
-------------------------------------------------------------------
I hope this makes sense. Essentially, I am trying to have two ORDER BY columns that are specific to a WHERE condition. Then once the correct ORDER BY column is chosen for that row, it orders the ROWS by the timestamp as a whole.
我希望这是有道理的。本质上，我试图有两个特定于 WHERE 条件的 ORDER BY 列。然后，一旦为该行选择了正确的 ORDER BY 列，它就会按整个时间戳对 ROWS 进行排序。
回答by RedFilter
SELECT id_one, id_two, timestamp_one, timestamp_two
FROM tablename
WHERE id_one = 27
OR id_two = 27
ORDER BY
CASE
WHEN id_one=27 THEN timestamp_one
WHEN id_two=27 THEN timestamp_two
END DESC
回答by buru
SELECT
id_one,
id_two,
(CASE
WHEN id_one=27 THEN timestamp_one
WHEN id_two=27 THEN timestamp_two
END) as timestamp
FROM tablename
ORDER BY timestamp DESC
回答by Esdras
SELECT * FROM tablename
WHERE id_one=27 OR id_two=27
ORDER BY id ASC, timestamp_one DESC
