MS-SQL Server
很沒有看到這樣寫的SQL，請解析一下
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区原帖: http://topic.csdn.net/u/20080926/12/316558f6-50ba-4633-b15f-80378d062809.html
-----------------------------------------------------------------------------------
請問下面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 不能運行?
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 不能運行?
...全文
