数据库结构如下:Id, Title, Showdate1 标题1 2010-8-19
2 null 2010-8-15
3 标题2 2010-8-20
4 null 2010-8-18
5 null 2010-8-16
6 标题6 2010-8-21现想按标题不为空的显示在前面,其次是按时间进行排序
如下:标题6 2010-8-21
标题2 2010-8-20
标题1 2010-8-19
null 2010-8-18
null
2 null 2010-8-15
3 标题2 2010-8-20
4 null 2010-8-18
5 null 2010-8-16
6 标题6 2010-8-21现想按标题不为空的显示在前面,其次是按时间进行排序
如下:标题6 2010-8-21
标题2 2010-8-20
标题1 2010-8-19
null 2010-8-18
null
order by
case when title is not null then 1 else 2 end,
Showdate
order by
title desc,
Showdate
select * from tborder by len(title) desc ,showdate
From
(
Select 1 AS [ID],'標題1' AS Title ,'2010-8-19' AS Showdate
Union
Select 2 , NULL,'2010-8-15'
Union
Select 3 ,'標題2','2010-8-20'
Union
Select 4 , NULL,'2010-8-18'
Union
Select 5 , NULL,'2010-8-16'
Union
Select 6 ,'標題6','2010-8-21'
)A
Order By Case When Title IS NULL Then 1 else 0 end ,Showdate desc/*
ID Title Showdate
----------- ----- ---------
6 標題6 2010-8-21
3 標題2 2010-8-20
1 標題1 2010-8-19
4 NULL 2010-8-18
5 NULL 2010-8-16
2 NULL 2010-8-15
*/