我有一个这样的数据库:
-------------------------------------------------------------------
| 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,它就会将各行作为一个整体按时间戳排序。
发布于 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
发布于 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 DESC
发布于 2015-04-29 00:54:28
SELECT * FROM tablename
WHERE id_one=27 OR id_two=27
ORDER BY id ASC, timestamp_one DESC
https://stackoverflow.com/questions/4346823
复制相似问题