php 您可以在 ORDER BY 中添加 if 语句吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3550942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can you add an if statement in ORDER BY?
提问by JM4
I am trying to achieve the following:
我正在努力实现以下目标：
I have a single ORDER BY statement which could vary depending on the value stored in Column A.
我有一个 ORDER BY 语句，它可能会因 A 列中存储的值而异。
For example:
例如：
if the Type is Member, sort by member last name if the Type is Group, sort by the Group Name
如果类型为成员，则按成员姓氏排序 如果类型为组，则按组名排序
both in Ascending order.
都按升序排列。
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?
我知道这是不正确的，但在其他地方找不到信息。有任何想法吗？
回答by ircmaxell
Well, you can use the IFfunctionin MySQL (Note the emphasis on functionsince there's also an unrelated IFstatement)...:
好吧，您可以在 MySQL 中使用该IF函数（注意强调，function因为还有一个不相关的IF语句）...：
ORDER BY IF(TYPE='Member', LNAME, GROUPNAME) ASC
However, in this case it seems the better choice (From a flexibility standpoint) would be the CASEstatement:
但是，在这种情况下，似乎更好的选择（从灵活性的角度来看）是以下CASE语句：
ORDER BY
CASE `type`
WHEN 'Member' THEN LNAME
WHEN 'Group' THEN GROUPNAME
ELSE 1 END
ASC
Note that the entire block from CASEto ENDis to be considered as a single "unit". The result of which is what you're trying to sort against (Hence why the ASCcomes after the block, rather than inside of it)...
请注意，从CASEto的整个块END将被视为单个“单元”。其结果就是您要针对的内容进行排序（因此为什么ASC在块之后而不是在块内部）...
回答by Andy
Use the CASE statement.
使用 CASE 语句。
Example from http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html:
来自http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html 的示例：
SELECT id, first_name, last_name, birthday
FROM table
ORDER BY
-- numeric columns
CASE _orderby WHEN 'id' THEN id END ASC,
CASE _orderby WHEN 'desc_ id' THEN id END DESC,
-- string columns
CASE _orderby WHEN 'first_name' THEN first_name WHEN 'last_name' THEN last_name END ASC,
CASE _orderby WHEN 'desc_first_name' THEN first_name WHEN 'desc_last_name' THEN last_name END DESC,
-- datetime columns
CASE _orderby WHEN 'birthday' THEN birthday END ASC,
CASE _orderby WHEN 'desc_ birthday' THEN birthday END DESC;
