很沒有看到這樣寫的SQL，請解析一下

csdyyr 2008-09-26 05:26:25

原帖: http://topic.csdn.net/u/20080926/12/316558f6-50ba-4633-b15f-80378d062809.html



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 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

/*

a                                                      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



(15 row(s) affected)

*/

-----------------------------------------------------------------------------------
請問下面ORDER BY 是甚麼意思?
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
而用 SELECT * FROM #tablea ORDER BY 0,a,b,c 不能運行?

...全文

101 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

hanjoe109 2008-09-30

打赏
举报

[Quote=引用 4 楼 commanager 的回复:]
学习了
[/Quote]

同感

dobear_0922 2008-09-26

打赏
举报

SELECT * FROM #tablea ORDER BY 1
相当于：SELECT * FROM #tablea ORDER BY a

dobear_0922 2008-09-26

打赏
举报

請問下面ORDER BY 是甚麼意思?
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的null數量倒序,再a,b,c排序

而用 SELECT * FROM #tablea ORDER BY 0,a,b,c 不能運行?

－－－－－－－
0在编译的时候会被理解为列号，列编号是从1开始的，0会报错，换成1或者2或者3都能执行（你的临时表有3列）

utpcb 2008-09-26

打赏
举报

按a,b,c的null數量倒序,再a,b,c排序

中国风 2008-09-26

打赏
举报

按a,b,c的null數量倒序,再a,b,c排序

commanager 2008-09-26

打赏
举报

学习了

chenjunsheep 2008-09-26

打赏
举报

ORDER BY后面是类似于拼字符串,
根据条件来生成最后的排序..

mugua604 2008-09-26

打赏
举报

可以看到语句结果..

 

SELECT 

    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 

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 

=----------------- 

          a                       b                       c

----------- ----------------------- ----------------------- -----------------------

0           NULL                    NULL                    NULL

1           2008-08-07 00:00:00.000 NULL                    NULL

1           2008-08-08 00:00:00.000 NULL                    NULL

2           2008-08-07 00:00:00.000 2008-08-08 00:00:00.000 NULL

2           2008-08-07 00:00:00.000 2008-08-09 00:00:00.000 NULL

2           2008-08-08 00:00:00.000 2008-08-08 00:00:00.000 NULL

2           2008-08-08 00:00:00.000 2008-08-09 00:00:00.000 NULL

3           2008-08-07 00:00:00.000 2008-08-08 00:00:00.000 2008-08-09 00:00:00.000

3           2008-08-07 00:00:00.000 2008-08-08 00:00:00.000 2008-08-10 00:00:00.000

3           2008-08-07 00:00:00.000 2008-08-09 00:00:00.000 2008-08-09 00:00:00.000

3           2008-08-07 00:00:00.000 2008-08-09 00:00:00.000 2008-08-10 00:00:00.000

3           2008-08-08 00:00:00.000 2008-08-08 00:00:00.000 2008-08-09 00:00:00.000

3           2008-08-08 00:00:00.000 2008-08-08 00:00:00.000 2008-08-10 00:00:00.000

3           2008-08-08 00:00:00.000 2008-08-09 00:00:00.000 2008-08-09 00:00:00.000

3           2008-08-08 00:00:00.000 2008-08-09 00:00:00.000 2008-08-10 00:00:00.000



(15 行受影响)

mugua604 2008-09-26

打赏
举报

这是一种排序..可以按
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
得到的值排序
你可以
SELECT
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
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
看下这个的结果..是先排序case when的后排序后面的~