MYSQL ORDER BY CASE问题
MYSQL ORDER BY CASE问题
提问于 2010-12-03 23:06:34
我有一个这样的数据库：
-------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------我需要把所有4行都拉出来
ORDER BY 'timestamp_one' if 'id_one'=27 or
ORDER BY 'timestamp_two' if 'id_one'=27这是我现在所拥有的声明：
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这在is输出中效果很好：
-------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------但我需要两个时间戳列来排序，就像它们是一个列一样，所以它的顺序是这样的：
-------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------我希望这是有意义的。本质上，我试图拥有两个特定于WHERE条件的ORDER BY列。然后，一旦为该行选择了正确的ORDER BY column，它就会将各行作为一个整体按时间戳排序。
回答 3
Stack Overflow用户
回答已采纳
发布于 2010-12-03 23:10:10
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 Stack Overflow用户
发布于 2010-12-03 23:17:07
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 DESCStack Overflow用户
发布于 2015-04-29 00:54:28
SELECT * FROM tablename
WHERE id_one=27 OR id_two=27
ORDER BY id ASC, timestamp_one DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4346823
复制相关文章
相似问题
腾讯云开发者
