sql server按etap和etac排序 [英] Sql server sort by etap and etac

问题描述

ETAp	         ETDp	      Change	 ETAc	         ETDc
3/2/2016 14:59	3/2/2016 15:00	Delete	 NULL	         NULL
3/15/2016 6:00	3/16/2016 6:00	Delete	 NULL	         NULL
3/12/2016 23:00	3/14/2016 7:00	SC	 3/15/2016 6:00	 3/16/2016 6:00
NULL	        NULL	        New	 4/12/2016 22:00 4/13/2016 16:00
4/15/2016 8:00	4/16/2016 5:00	SC	 4/14/2016 20:00 4/15/2016 3:00
4/14/2016 20:00	4/15/2016 3:00	SC	 4/15/2016 8:00	 4/16/2016 5:00
NULL	        NULL	        New	 4/23/2016 14:30 4/23/2016 21:30

我需要根据ETAp和ETAc进行排序。我根据查询排序



有人可以建议。



我试过的：



选择etap，etap，change，etac，etdc来自sch order，当ETAp为null然后是1 else 0 End，ETAp，case when when ETAc为null然后1其他0结束，ETAc



但结果不是我预期的结果。



我的预期产量是

I need to sort based on ETAp and ETAc. i have sorted based on the query

Can anyone suggest.

What I have tried:

Select etap,etap,change,etac,etdc from sch order by case when ETAp Is null then 1 else 0 End ,ETAp ,case when ETAc Is null then 1 else 0 End ,ETAc

but the result is not expected as i prefered.

My expected output is

ETAp	        ETDp	       Change	  ETAc	          ETDc
3/2/2016 14:59	3/2/2016 15:00	 Delete	  NULL	           NULL
3/12/2016 23:00	3/14/2016 7:00	 SC	  3/15/2016 6:00   3/16/2016 6:00
3/15/2016 6:00	3/16/2016 6:00	 Delete	  NULL	           NULL
NULL	        NULL	         New	  4/12/2016 22:00  4/13/2016 16:00
4/15/2016 8:00	4/16/2016 5:00	 SC	  4/14/2016 20:00  4/15/2016 3:00
4/14/2016 20:00	4/15/2016 3:00	 SC	  4/15/2016 8:00   4/16/2016 5:00
NULL	        NULL	         New	  4/23/2016 14:30  4/23/2016 21:30

推荐答案

而不是在ORDER BY子句尝试使用ISNULL函数。



我花了一点时间来发现我做错了什么，因为我一直得到结果的第5行和第6行但是，此查询会产生您期望的结果：

Rather than using CASE in the ORDER BY clause try using the ISNULL function.

It took me a little while to spot what I was doing wrong as I kept getting the 5th and 6th lines of the result in the wrong order, however this query produces the results you expect:

select *
from sch
order by ISNULL(ETAc,ETAp) , ISNULL(ETAp,ETAc) 

注意事项：

- 我根据实际值进行排序ETAc和ETAp而不是硬编码值为0和1.

- 如果其中一个日期时间值为NULL，我将其替换为其他日期值（在排序中）

- 获取结果你发布了我必须首先按ETAc排序，然后按ETAp排序。

Things to note:
- I'm sorting based on the actual values of ETAc and ETAp rather than on hard-coded values of 0 and 1.
- If one of the datetime values is NULL I'm replacing it with the other datevalue (in the sort)
- To get the results you posted I had to sort by ETAc first, then by ETAp.

这篇关于sql server按etap和etac排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！