MySQL查询与复杂的ORDER BY

I have the following query:

我有以下查询:

SELECT id, title, adcontent, adtargetURL, locationpage, 
locationarg, locationid, displayorder, visible, schedstart, 
schedstop, created, modified, createdby, modifiedby
FROM ads            
ORDER BY locationpage, locationarg, locationid, title

I need to order the fields the following way:

我需要按以下方式订购字段:

sort by locationpage with any fields with a value of 'all' first, then the rest in ascending order

按位置排序,首先选择值为'all'的任何字段,然后按升序排序

then by locationarg with any NULL or empty string values first, then the rest in asc

然后通过locationarg首先使用任何NULL或空字符串值,然后在asc中使用其余值

then by locationid with any NULL or 0 values first, the rest in asc

然后通过locationid首先使用任何NULL或0值,其余的在asc中

and within those, sort by displayorder of '1' first, then NULL, then '2'

在那些之内,首先按'1'的显示顺序排序,然后是NULL,然后是'2'

and lastly, by title in ASC if any of those manage to all be the same

最后,如果其中任何一个都设置为相同,则通过ASC中的标题

What should my ORDER BY look like to achieve this?

我的ORDER BY应该如何实现这一目标?

Here's what I have so far: (updated from below)

这是我到目前为止:(从下面更新)

ORDER BY locationpage='all', 
         locationpage ASC, 
         locationarg ASC, 
         locationid ASC, 
         displayorder='1', 
         ISNULL(displayorder), 
         displayorder='2', 
         title ASC

...but this isn't working!

......但这不起作用!

2 个解决方案

#1

Your best choice would be to use a calculated field that will generate order id according to your rules.. I'll add an example in a moment..

您最好的选择是使用一个计算字段,根据您的规则生成订单ID ..我稍后会添加一个示例..

select
case
  when locationpage = "all" then 10
  when ISNULL(locationarg)  then 20
  ....
  else 50 
end as OrderID,
id, 
title, 
....

FROM ads            

ORDER BY OrderID DSEC

#2

Give this a bash:

给这个打击:

select id, title, adcontent, adtargetURL, locationpage, locationarg, locationid, displayorder, visible, schedstart, schedstop, created, modified, createdby, modifiedby 
from ads
order by case when locationpage='all' then 0 else 1 end, 
         locationpage, 
         case when locationarg is null or locationarg='' then 0 else 1 end,
         locationarg ASC, 
         case when locationid is null OR locationid='0' then 0 else 1 end, 
         locationid, 
         case when displayorder =1 then 0 when displayorder is null then 1 when displayorder = 2 then 2 else 3 end,
         title;

#1