大佬们，怎么让数据正常排序，空值放到最后面

[qq_31193725]

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([Name] nvarchar(23),[ExtenField1] int)
Insert #T
select N'测试1',0 union all
select N'测试2',1 union all
select N'测试3',null union all
select N'测试4',2 union all
select N'测试5',null union all
select N'测试6',3
Go
--测试数据结束
SELECT  *
FROM    #T
ORDER BY CASE WHEN ExtenField1 IS NULL THEN 10000000
              ELSE ExtenField1
         END

[听雨停了]

WITH cte AS (
SELECT '0' AS t UNION ALL
SELECT '1' UNION ALL
SELECT '' UNION ALL
SELECT '2'
)
SELECT * FROM cte
ORDER BY (CASE t WHEN '' THEN '9999' END)

大概这样吧

[文盲老顾]

declare @t table(id int identity,num varchar(max))
insert into @t(num) values(null),('1'),('22'),('12'),(''),('0')
select * from @t order by (case when isnull(num,'')='' then 1 else 0 end),num

[文盲老顾]

declare @t table(id int identity,num varchar(max))
insert into @t(num) values(null),('1'),('22'),('12'),(''),('0'),('211')
select * from @t order by (case when isnull(num,'')='' then 1 else 0 end),(case when isnull(num,'')='' then 0 else convert(int,num) end)

[吉普赛的歌]

DECLARE @t TABLE ( id INT, c1 NVARCHAR(20))
INSERT INTO @t VALUES (1,'a')
INSERT INTO @t VALUES (2,NULL)
INSERT INTO @t VALUES (3,'b')
INSERT INTO @t VALUES (4,'c')
--如果你的数据没什么规律，不好象楼上各位那样排序（假设NULL的值），
--可以两次排序，先让 c1 为 null 时就排到末尾再说，这样可保证万无一失
SELECT * FROM @t
ORDER BY 
	CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
	,c1
/*
id	c1
1	a
3	b
4	c
2	NULL
*/

[吉普赛的歌]

哦， 老顾的想法跟我类似

[qq_31193725]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([Name] nvarchar(23),[ExtenField1] int)
Insert #T
select N'测试1',0 union all
select N'测试2',1 union all
select N'测试3',null union all
select N'测试4',2 union all
select N'测试5',null union all
select N'测试6',3
Go
--测试数据结束
SELECT  *
FROM    #T
ORDER BY CASE WHEN ExtenField1 IS NULL THEN 10000000
              ELSE ExtenField1
         END

能不能只把空值排除到前面是0,1,4,3这么排

[二月十六]

这样试试，借鉴了老顾和yenange的想法

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([Name] nvarchar(23),[ExtenField1] int)
Insert #T
select N'测试1',0 union all
select N'测试2',1 union all
select N'测试3',null union all
select N'测试4',4 union all
select N'测试5',null union all
select N'测试6',3
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( ORDER BY GETDATE() ) num
FROM    #T
)
SELECT  Name ,
        ExtenField1
FROM    cte
ORDER BY CASE WHEN ExtenField1 IS NULL THEN 0
              ELSE 1
         END ,
        num

[听雨停了]

引用 8 楼 sinat_28984567 的回复:

这样试试，借鉴了老顾和yenange的想法

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([Name] nvarchar(23),[ExtenField1] int)
Insert #T
select N'测试1',0 union all
select N'测试2',1 union all
select N'测试3',null union all
select N'测试4',4 union all
select N'测试5',null union all
select N'测试6',3
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( ORDER BY GETDATE() ) num
FROM    #T
)
SELECT  Name ,
        ExtenField1
FROM    cte
ORDER BY CASE WHEN ExtenField1 IS NULL THEN 0
              ELSE 1
         END ,
        num

楼主的意思跟上次一个帖子一样，他大概是要把null排在最后，然后按照name再去排序，这样才会得到0，1，4，3，null的排序结果。下班了回家喽

[吉普赛的歌]

引用 9 楼 qq_37170555 的回复:

[quote=引用 8 楼 sinat_28984567 的回复:] 这样试试，借鉴了老顾和yenange的想法

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([Name] nvarchar(23),[ExtenField1] int)
Insert #T
select N'测试1',0 union all
select N'测试2',1 union all
select N'测试3',null union all
select N'测试4',4 union all
select N'测试5',null union all
select N'测试6',3
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( ORDER BY GETDATE() ) num
FROM    #T
)
SELECT  Name ,
        ExtenField1
FROM    cte
ORDER BY CASE WHEN ExtenField1 IS NULL THEN 0
              ELSE 1
         END ,
        num

楼主的意思跟上次一个帖子一样，他大概是要把null排在最后，然后按照name再去排序，这样才会得到0，1，4，3，null的排序结果。下班了回家喽[/quote] 17：30 就下班？

[文盲老顾]

同下班，回家看孩子

[二月十六]

引用 9 楼 qq_37170555 的回复:

[quote=引用 8 楼 sinat_28984567 的回复:] 这样试试，借鉴了老顾和yenange的想法

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([Name] nvarchar(23),[ExtenField1] int)
Insert #T
select N'测试1',0 union all
select N'测试2',1 union all
select N'测试3',null union all
select N'测试4',4 union all
select N'测试5',null union all
select N'测试6',3
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( ORDER BY GETDATE() ) num
FROM    #T
)
SELECT  Name ,
        ExtenField1
FROM    cte
ORDER BY CASE WHEN ExtenField1 IS NULL THEN 0
              ELSE 1
         END ,
        num

楼主的意思跟上次一个帖子一样，他大概是要把null排在最后，然后按照name再去排序，这样才会得到0，1，4，3，null的排序结果。下班了回家喽[/quote] 如果是这个意思就这样

;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( ORDER BY GETDATE() ) num
FROM    #T
)
SELECT  Name ,
        ExtenField1
FROM    cte
ORDER BY CASE WHEN ExtenField1 IS NULL THEN 1
              ELSE 0
         END ,
        num

我能说我5:00就下班了么。。。