MYSQL ORDER BY CASE问题 [英] MYSQL ORDER BY CASE Issue

问题描述

我有一个这样的数据库:

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       |
-------------------------------------------------------------------

我需要拉全部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

这很好，因为它输出以下内容:

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       |
-------------------------------------------------------------------

我希望这是有道理的.本质上，我试图拥有两个特定于WHERE条件的ORDER BY列.然后，为该行选择正确的ORDER BY列后，它将按时间戳整体对ROWS进行排序.

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.

推荐答案

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 

这篇关于MYSQL ORDER BY CASE问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！