Order by 原来可以这样用，涨姿势啦

昨天遇到一个奇葩的order 排序问题，不过真让我大开眼界：

    查询Students表， 对Gender字段进行排序。（Gender类型修改为smallint，允许值0-3）

    要求： Gender=1优先排序，其余按照出生日期：Birthday降序排列

常用的只有 order by col-name [asc/desc]. 优先排序，这不是非要在order中插入条件判断，怎么可能？？ But Nothing is Impossible!

1、插入测试数据：（DataAdd 参考： http://technet.microsoft.com/zh-cn/library/ms186819.aspx）

1 -- Insert some samples
2 --select FLOOR(RAND()*10)  -- a random number, ranging from 0 to 9
3 Declare @randNum varchar(1), @date varchar(10)
4 Set @randNum = FLOOR(RAND()*10) 
5 Set @date = '199' + @randNum + '-01-01'
6 
7 insert into Students
8 values('Tom_'+@randNum, FLOOR(RAND()*4) ,DATEADD(m,  FLOOR(RAND()*10)  + 1,@date))
9 go 50

2、自定义order by 条件判断实现排序：（参考： http://www.tutorialspoint.com/sql/sql-sorting-results.htm）

1 select * from Students
2 order by (Case Gender When 1 Then 0 Else 1 End) asc, Birthday desc

数据显示为：

结果刚刚好。虽然符合了需求，但第一次这样用，万分不理解。条件指定为0,1就可以，那其他的数字呢，还是说有什么默认的限制？经过不断的测试，算是有点小眉目啦。

Order by 中的 Case 语句，相当于是自定义排序的实现方法。 用’冒泡’想想，就很容易理解了。

如果要实现升序排列，两个数的比较，越小的，排列的位置越靠前，所以只需要给优先排序的字段最低的’权值’， 其他的依次排列。

同理就可以实现新的需求： Gender=1 优先， 3 其次， 其他 Birth 降序

1 select * from Students
2 order by (
3     Case Gender            -- set the priority: (asc - lower)
4         When 1 Then -1 
5         When 3 Then 1  
6         Else 2 
7     End) asc, Birthday desc

当时又突然冒出了另一个问题，直接用order by num. 是个什么效果？恕我孤陋寡闻，以前真的不知道还有 order by [col-index]， 现在才明白数字是列索引， 可以在一般意义上等价于列名称。

参考：http://www.cnblogs.com/cuimingda/archive/2007/04/10/707237.html

[既然把多行给union了，把每列叫成什么名字都是不妥当的。。。只要采用列的序号即可, 序号从1开始。]

参考： http://technet.microsoft.com/zh-c·n/library/ms188723(v=sql.105).aspx

[如果列名已在 SELECT 列表中有了别名，则 ORDER BY 子句中只能使用别名。]

1 select StuID, StuName, Gender as Sex from Students
2 order by Sex  -- equal to  col index:  3  or the original col : Gender 

但下面的这个，使用列名做为排序条件，仍然可以查询？

1 select StuID, StuName from Students  -- ok, the right data
2 order by Birthday desc
3 
4 select * from Students
5 order by Birthday desc
6 
7 --select StuID, StuName from Students
8 --order by 4 desc  --- Error: ORDER BY 位置号 4 超出了选择列表中项数的范围

后来后来才找到真正重量级的解释：http://msdn.microsoft.com/en-us/library/e9zc0283(v=vs.80).aspx

 

 

转载于:https://www.cnblogs.com/ceeekor/p/3675224.html