datetime 类型数据排序问题

Question

create table #tablea
(
a datetime null,
b datetime null,
c datetime null
)insert into #tablea(a,b,c) values('2008-08-08','2008-08-09','2008-08-10')
insert into #tablea(a,b,c) values('2008-08-08','2008-08-09','2008-08-09')
insert into #tablea(a,b,c) values('2008-08-08','2008-08-09',null)insert into #tablea(a,b,c) values('2008-08-08','2008-08-08','2008-08-10')
insert into #tablea(a,b,c) values('2008-08-08','2008-08-08','2008-08-09')
insert into #tablea(a,b,c) values('2008-08-08','2008-08-08',null)insert into #tablea(a,b,c) values('2008-08-08',null,null)
------------------------------------------------------------------------------------
insert into #tablea(a,b,c) values('2008-08-07','2008-08-09','2008-08-10')
insert into #tablea(a,b,c) values('2008-08-07','2008-08-09','2008-08-09')
insert into #tablea(a,b,c) values('2008-08-07','2008-08-09',null)insert into #tablea(a,b,c) values('2008-08-07','2008-08-08','2008-08-10')
insert into #tablea(a,b,c) values('2008-08-07','2008-08-08','2008-08-09')
insert into #tablea(a,b,c) values('2008-08-07','2008-08-08',null)insert into #tablea(a,b,c) values('2008-08-07',null,null)
-----------------------------------------------------------------------------------
insert into #tablea(a,b,c) values(null,null,null)
上面是测试的数据表结构和数据希望得到的排序结果为<table>
<tr><td>null</td><td>null</td><td>null</td></tr><tr><td>2008-08-07</td><td>null</td><td>null</td></tr>
<tr><td>2008-08-08</td><td>null</td><td>null</td></tr><tr><td>2008-08-07</td><td>2008-08-08</td><td>null</td></tr>
<tr><td>2008-08-07</td><td>2008-08-09</td><td>null</td></tr>
<tr><td>2008-08-08</td><td>2008-08-08</td><td>null</td></tr>
<tr><td>2008-08-08</td><td>2008-08-09</td><td>null</td></tr><tr><td>2008-08-07</td><td>2008-08-08</td><td>2008-08-09</td></tr>
<tr><td>2008-08-07</td><td>2008-08-08</td><td>2008-08-10</td></tr>
<tr><td>2008-08-07</td><td>2008-08-09</td><td>2008-08-09</td></tr>
<tr><td>2008-08-07</td><td>2008-08-09</td><td>2008-08-10</td></tr><tr><td>2008-08-08</td><td>2008-08-08</td><td>2008-08-09</td></tr>
<tr><td>2008-08-08</td><td>2008-08-08</td><td>2008-08-10</td></tr>
<tr><td>2008-08-08</td><td>2008-08-09</td><td>2008-08-09</td></tr>
<tr><td>2008-08-08</td><td>2008-08-09</td><td>2008-08-10</td></tr>
</table>
请高手帮忙，谢谢!O(∩_∩)O

Answer 1

晕！结果表数据如下：
a b c
null null null
2008-08-07 null null
2008-08-08 null null
2008-08-07 2008-08-08 null
2008-08-07 2008-08-09 null
2008-08-08 2008-08-08 null
2008-08-08 2008-08-09 null
2008-08-07 2008-08-08 2008-08-09
2008-08-07 2008-08-08 2008-08-10
2008-08-07 2008-08-09 2008-08-09
2008-08-07 2008-08-09 2008-08-10
2008-08-08 2008-08-08 2008-08-09
2008-08-08 2008-08-08 2008-08-10
2008-08-08 2008-08-09 2008-08-09
2008-08-08 2008-08-09 2008-08-10

Answer 2

SELECT 
*
FROM #tablea
ORDER BY 
CASE WHEN a IS NULL THEN 0 ELSE 1 END +
CASE WHEN b IS NULL THEN 0 ELSE 1 END +
CASE WHEN c IS NULL THEN 0 ELSE 1 END,
a, b, c

Answer 3

create table #tablea
(
    a datetime null,
    b datetime null,
    c datetime null
)insert into #tablea(a,b,c) values('2008-08-08','2008-08-09','2008-08-10')
insert into #tablea(a,b,c) values('2008-08-08','2008-08-09','2008-08-09')
insert into #tablea(a,b,c) values('2008-08-08','2008-08-09',null)insert into #tablea(a,b,c) values('2008-08-08','2008-08-08','2008-08-10')
insert into #tablea(a,b,c) values('2008-08-08','2008-08-08','2008-08-09')
insert into #tablea(a,b,c) values('2008-08-08','2008-08-08',null)insert into #tablea(a,b,c) values('2008-08-08',null,null)
------------------------------------------------------------------------------------
insert into #tablea(a,b,c) values('2008-08-07','2008-08-09','2008-08-10')
insert into #tablea(a,b,c) values('2008-08-07','2008-08-09','2008-08-09')
insert into #tablea(a,b,c) values('2008-08-07','2008-08-09',null)insert into #tablea(a,b,c) values('2008-08-07','2008-08-08','2008-08-10')
insert into #tablea(a,b,c) values('2008-08-07','2008-08-08','2008-08-09')
insert into #tablea(a,b,c) values('2008-08-07','2008-08-08',null)insert into #tablea(a,b,c) values('2008-08-07',null,null)
-----------------------------------------------------------------------------------
insert into #tablea(a,b,c) values(null,null,null)
select * from #tablea order by (case when a is null then 0  when b is null then 1 when c is null then 2 else 3 end),a,b,c
drop table #tableaa                                                      b                                                      c                                                      
------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 
NULL                                                   NULL                                                   NULL
2008-08-07 00:00:00.000                                NULL                                                   NULL
2008-08-08 00:00:00.000                                NULL                                                   NULL
2008-08-07 00:00:00.000                                2008-08-08 00:00:00.000                                NULL
2008-08-07 00:00:00.000                                2008-08-09 00:00:00.000                                NULL
2008-08-08 00:00:00.000                                2008-08-08 00:00:00.000                                NULL
2008-08-08 00:00:00.000                                2008-08-09 00:00:00.000                                NULL
2008-08-07 00:00:00.000                                2008-08-08 00:00:00.000                                2008-08-09 00:00:00.000
2008-08-07 00:00:00.000                                2008-08-08 00:00:00.000                                2008-08-10 00:00:00.000
2008-08-07 00:00:00.000                                2008-08-09 00:00:00.000                                2008-08-09 00:00:00.000
2008-08-07 00:00:00.000                                2008-08-09 00:00:00.000                                2008-08-10 00:00:00.000
2008-08-08 00:00:00.000                                2008-08-08 00:00:00.000                                2008-08-09 00:00:00.000
2008-08-08 00:00:00.000                                2008-08-08 00:00:00.000                                2008-08-10 00:00:00.000
2008-08-08 00:00:00.000                                2008-08-09 00:00:00.000                                2008-08-09 00:00:00.000
2008-08-08 00:00:00.000                                2008-08-09 00:00:00.000                                2008-08-10 00:00:00.000

Answer 4

select * from #tablea order by isnull(c,'1900-1-1'),isnull(b,'1900-1-1'),a

Answer 5

其实大家都在猜。没有null '2008-8-8' '2008-8-8'
或
null '2008-8-8' null
或
null null '2008-8-8'这样的数据， 大家都只能猜想楼主的规则是什么样的。

Answer 6

SELECT 
    *
FROM #tablea
ORDER BY 
    CASE WHEN a IS NULL THEN 0 ELSE 1 END +
    CASE WHEN b IS NULL THEN 0 ELSE 1 END +
    CASE WHEN c IS NULL THEN 0 ELSE 1 END,
    a, b, c

Answer 7

select * from #tablea order by c,b,aa,b,c
,,
2008-08-07 00:00:00.000,,
2008-08-08 00:00:00.000,,
2008-08-07 00:00:00.000,2008-08-08 00:00:00.000,
2008-08-08 00:00:00.000,2008-08-08 00:00:00.000,
2008-08-07 00:00:00.000,2008-08-09 00:00:00.000,
2008-08-08 00:00:00.000,2008-08-09 00:00:00.000,
2008-08-07 00:00:00.000,2008-08-08 00:00:00.000,2008-08-09 00:00:00.000
2008-08-08 00:00:00.000,2008-08-08 00:00:00.000,2008-08-09 00:00:00.000
2008-08-07 00:00:00.000,2008-08-09 00:00:00.000,2008-08-09 00:00:00.000
2008-08-08 00:00:00.000,2008-08-09 00:00:00.000,2008-08-09 00:00:00.000
2008-08-07 00:00:00.000,2008-08-08 00:00:00.000,2008-08-10 00:00:00.000
2008-08-08 00:00:00.000,2008-08-08 00:00:00.000,2008-08-10 00:00:00.000
2008-08-07 00:00:00.000,2008-08-09 00:00:00.000,2008-08-10 00:00:00.000
2008-08-08 00:00:00.000,2008-08-09 00:00:00.000,2008-08-10 00:00:00.000（所影响的行数为 15 行）

Answer 8

大体的规则就是
a,b,c
表示3个时间点，是按时间顺序排列的a<b<c。如果a没有的话b,c就没有什么意义，b没有的话c也就没什么意义。所以月儿弯弯照九州提到的数据形式暂时不考虑。O(∩_∩)O排序规则：空白的越多越靠前。再按时间排。