您可以在 ORDER BY 中添加 if 语句吗? [英] Can you add an if statement in ORDER BY?

问题描述

我正在努力实现以下目标:

I am trying to achieve the following:

我有一个 ORDER BY 语句，它可能会因 A 列中存储的值而异.

I have a single ORDER BY statement which could vary depending on the value stored in Column A.

例如:

如果类型为成员，则按成员姓氏排序如果类型为组，则按组名排序

if the Type is Member, sort by member last name if the Type is Group, sort by the Group Name

均按升序排列.

我对最终声明的最佳猜测是:

My best guess for the final statement would be:

SELECT * 
  FROM table 
 WHERE STATUS = 'Active' 
 ORDER BY ((LNAME if TYPE = 'Member') OR (GROUPNAME if TYPE = 'Group')) ASC

我知道这是不正确的，但在其他地方找不到信息.有什么想法吗?

I know this is incorrect but cannot find information elsewhere. Any ideas?

推荐答案

好吧，你可以使用IF function 在 MySQL 中(注意强调 function 因为还有一个不相关的IF 语句)...:

Well, you can use the IF function in MySQL (Note the emphasis on function since there's also an unrelated IF statement)...:

ORDER BY IF(TYPE='Member', LNAME, GROUPNAME) ASC

然而，在这种情况下，似乎更好的选择(从灵活性的角度来看)是 CASE 声明:

However, in this case it seems the better choice (From a flexibility standpoint) would be the CASE statement:

ORDER BY 
    CASE `type` 
        WHEN 'Member' THEN LNAME 
        WHEN 'Group' THEN GROUPNAME
        ELSE 1 END 
    ASC

请注意，从 CASE 到 END 的整个块将被视为一个单元".结果就是你想要排序的结果(因此为什么 ASC 出现在块之后，而不是在块内部)...

Note that the entire block from CASE to END is to be considered as a single "unit". The result of which is what you're trying to sort against (Hence why the ASC comes after the block, rather than inside of it)...

这篇关于您可以在 ORDER BY 中添加 if 语句吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！