order by 特殊排序技巧

if object_id('tempdb..#temp') is not null

    drop table #temp  

create table #temp(col1 varchar(100), col2 varchar(100))

insert into #temp

select 'X68.23',''

union all select 'X86.32',''

union all select 'ZA11.30',''

union all select 'ZB11.47',''

go  

------ 需求一、按col 4，1，2排序

-- 用 charindex

select col1,col2 from #temp

order by charindex(col2,'4,1,2')  

-- order by 中用 case

select col1,col2 from #temp

order by

    case col2

        when '' then 1

        when '' then 2

        when '' then 3

        else 3 + rand()

    end  

-- 用 union

select col1,col2 from #temp where col2 = '' union all

select col1,col2 from #temp where col2 = '' union all

select col1,col2 from #temp where col2 = ''  

------ 需求二、 col 4 排第一，其余随便

select col1,col2 from #temp

order by

    case col1

        when '' then 1

        else 1 + rand()

    end  

------ 需求三、 随机排序

select col1,col2 from #temp

order by newid()

if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp(col1 varchar(100), col2 varchar(100))
insert into #temp
select 'X68.23','4'
union all select 'X86.32','2'
union all select 'ZA11.30','1'
union all select 'ZB11.47','1'
go
------ 需求一、按col 4，1，2排序
-- 用 charindex
select col1,col2 from #temp
order by charindex(col2,'4,1,2')
-- order by 中用 case
select col1,col2 from #temp
order by
case col2
when '4' then 1
when '1' then 2
when '2' then 3
else 3 + rand()
end
-- 用 union
select col1,col2 from #temp where col2 = '4' union all
select col1,col2 from #temp where col2 = '1' union all
select col1,col2 from #temp where col2 = '2'
------ 需求二、 col 4 排第一，其余随便
select col1,col2 from #temp
order by
case col1
when '4' then 1
else 1 + rand()
end
------ 需求三、随机排序
select col1,col2 from #temp
order by newid()
ORDER BY
case when state=5 then 2 else 1 end ASC,
weight_op_time DESC

巴特西

order by 特殊排序技巧

最新文章

热门文章