php - MySql ORDER BY 多列 IF AND

Question

所以我为特定查询连接了两个表。在一个复杂的 SELECT 语句之后，表格看起来大概像这样:$myid = 3

PostID | FromUserID | To User ID|Date     | PostSeen | NumLikes  |SeenLike61     |     48     |     3     | 5/11/13 |    1     |     1     |  159     |     3      |     3     | 4/11/13 |    1     |     1     |  058     |     3      |     3     | 4/11/13 |    1     |     1     |  125     |     47     |     3     | 3/11/13 |    1     |     2     |  053     |     56     |     3     | 2/11/13 |    0     |     3     |  121     |     3      |     55    | 1/11/13 |    1     |     0     |  null20     |     56     |     3     | 30/10/13|    1     |     0     |  null18     |     47     |     3     | 29/10/13|    0     |     0     |  null

If NumLikes is equal to 0 then the SeenLike column is null. The PostSeen will always be 0 or 1. Ive put the date in a simplified format here, but its proper DateTimed.

Essentially, I want do something that is the equivalent of

if(FromUserID == $myid && NumLikes > 0){
ORDER BY SeenLike ASC, Date DESC
}
if (FromUserID != $myid) {
ORDER BY PostSeen ASC, Date DESC
}

(我知道这是完全错误的，只是为了模糊地说明我的目标)这样它就会对结果进行排序，如下所示:

PostID | FromUserID | To User ID|Date     | PostSeen | NumLikes  |SeenLike59     |     3      |     3     | 4/11/13 |    1     |     1     |  025     |     47     |     3     | 3/11/13 |    1     |     2     |  053     |     56     |     3     | 2/11/13 |    0     |     3     |  118     |     47     |     3     | 29/10/13|    0     |     0     |  null61     |     48     |     3     | 5/11/13 |    1     |     1     |  158     |     3      |     3     | 4/11/13 |    1     |     1     |  121     |     3      |     55    | 1/11/13 |    1     |     0     |  null20     |     56     |     3     | 30/10/13|    1     |     0     |  null

我该怎么做呢？我试过 IF 和 CASE，但到目前为止都没有用。也将不胜感激对任何答案的解释!感谢您的帮助!

Answer 1

您可以创建两个子查询，然后将它们 UNION。第一个子查询将包含“WHERE FromUserID = $myid AND NumLikes > 0 ORDER BY SeenLike ASC, Date DESC”和第二个“WHERE FromUserID != $myid ORDER BY PostSeen ASC, Date DESC”。