如何在ORDER BY中使用CASE函数?

问题描述

我的朋友问了一个问题几次.在此之下也有一个答案，这是很好的，但对我而言并非如此.该解决方案的想法是将当前表自身连接起来.这对我来说似乎很昂贵，但效果不佳，因为实际上我的查询中的这些表上有四个join(votes，favorites，comments，viewed).

My friend asked a question a few times ago. Also there is a answer under that and it is good, but not for my case. The idea of that solution is joining the current table to itself. That seems expensive and not effective for me, Because in reality there is four join on these tables (votes, favorites, comments, viewed) in my query.

现在我想知道，如何使用CASE函数来做到这一点?像这样:

Now I want to know, how can I do that using CASE function? Something like this:

... ORDER BY Type, CASE WHEN AcceptedAnswerId = Id THEN 1 ELSE 0, timestamp

还是有更好的解决方案?

Or is there any better solution?

为便于阅读，我将这些示例粘贴在这里:

To be more readable, I paste those examples here:

我有一个这样的表:

// Mytable
+----+--------------------+------+------------------+-----------+
| Id |  QuestionOrAnswer  | Type | AcceptedAnswerId | timestamp |
+----+--------------------+------+------------------+-----------+
| 1  | question1          | 0    | 3                | 1         |
| 2  | answer1            | 1    | NULL             | 2         |
| 3  | answer2            | 1    | NULL             | 3         | -- accepted answer
| 4  | answer3            | 1    | NULL             | 4         |
+----+--------------------+------+------------------+-----------+

现在我想要这个结果:(请关注订单)

Now I want this result: (please focus on the order)

+----+--------------------+------+------------------+-----------+
| Id |  QuestionOrAnswer  | Type | AcceptedAnswerId | timestamp |
+----+--------------------+------+------------------+-----------+
| 1  | question1          | 0    | 3                | 1         |
| 3  | answer2            | 1    | NULL             | 3         | -- accepted answer
| 2  | answer1            | 1    | NULL             | 2         |
| 4  | answer3            | 1    | NULL             | 4         |
+----+--------------------+------+------------------+-----------+
//                          ^ 0 means question and 1 means answer

推荐答案

CASE可以使用，但是您缺少END.但是在这种情况下，您也可以只使用IF(AcceptedAnswerId = Id,1,0).

CASE would work, but you are missing the END. But in this case, you could also just use IF(AcceptedAnswerId = Id,1,0).

在您显示的简单情况下，您可以进行操作:

In the simple case you show, you could just do:

order by type,if(type=0,(@accepted:=acceptedanswerid),id<>@accepted),timestamp

但我不知道这在您的实际情况下是否行得通.

but I don't know if that would work in your real case.

这篇关于如何在ORDER BY中使用CASE函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！