我有一个这样的数据库：
------------------------------------------------------------------- | 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
这很好，因为输出如下：
------------------------------------------------------------------- | 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 | -------------------------------------------------------------------
但是我需要两个timestamp列进行排序，就像它们是一列一样，因此它的排序如下：
------------------------------------------------------------------- | 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列后，它将按时间戳整体对ROWS进行排序。
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
