MsSql综合技巧:很沒有看到這樣寫地SQL，請解释分析一下[库库中文网]-数据库类教程 QQGB.com

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

SQL code
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 不能運行?

这个问题第1个回答：
这是一种排序..可以按
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的后排序后面的~

这个问题第2个回答：
可以看到语句结果..

SQL code

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

[1] [2] 下一页