select city from base where city='上海' UNION ALL select city from base where city<>'上海'注意别排序
--> 测试数据:@table declare @table table([city] varchar(4)) insert @table select '北京' union all select '大连' union all select '上海' union all select '天津' union all select '上海' union all select '大连'select * from @table order by case [city] when '上海' then 0 else 1 end ----------------------- 上海 上海 大连 天津 北京 大连
谢谢你 但是我不想用UNION 效率不好
select city from base order by case '上海' when 1 else 0 end
--> 测试数据:@table declare @table table([city] varchar(4)) insert @table select '北京' union all select '大连' union all select '上海' union all select '天津' union all select '上海' union all select '大连'select * from @table order by case [city] when '上海' then 'A' else [city] end --------------------- 上海 上海 北京 大连 大连 天津
select * from tb order by case [city] when '上海' then 'A' else [city] end
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-12-10 09:31:37 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(4)) insert [tb] select '北京' union all select '大连' union all select '上海' union all select '天津' union all select '上海' union all select '大连' --------------开始查询-------------------------- select * from tb order by case col when '上海' then 0 else 1 end----------------结果---------------------------- /*col ---- 上海 上海 大连 天津 北京 大连(6 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-12-10 09:31:37 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(4)) insert [tb] select '北京' union all select '大连' union all select '上海' union all select '天津' union all select '上海' union all select '大连' --------------开始查询-------------------------- select * from tb order by case col when '上海' then 'a' else col end----------------结果---------------------------- /*col ---- 上海 上海 北京 大连 大连 天津(6 行受影响) */
CREATE TABLE BASE ( CITY VARCHAR(8) ) GOINSERT INTO BASE SELECT '北京' UNION ALL SELECT '大连' UNION ALL SELECT '上海' UNION ALL SELECT '天津' UNION ALL SELECT '上海' UNION ALL SELECT '大连' GOSELECT * FROM BASE ORDER BY CHARINDEX(CITY,'上海北京天津大连')
select city from base order by case '上海' when 1 else 0 end
select city from base order by case '上海' when 1 else 0 end
select city from base order by case '上海' when 1 else 0 end 这招狠! 哈哈哈!
有特殊的排序需求,比如用查询的结果去填事先定制的excel表格,都是用 order by when case 这样的方法.
用游标可以实现: delete from #temp declare @city varchar(10),@city1 varchar(10) --create table #temp(city varchar(10)) declare yb1 scroll cursor for select city from base open yb1 fetch relative 3 from yb1 into @city select @city1=@city insert into #temp select @city1 fetch relative 2 from yb1 into @city select @city1=@city insert into #temp select @city1 fetch relative -4 from yb1 into @city select @city1=@city insert into #temp select @city1 fetch relative 1 from yb1 into @city select @city1=@city insert into #temp select @city1 fetch relative 2 from yb1 into @city select @city1=@city insert into #temp select @city1 fetch relative 2 from yb1 into @city select @city1=@city insert into #temp select @city1 select * from #temp close yb1 deallocate yb1
SQL默认是asc 所以是先0 在1 select city from base order by case '上海' when 0 else 1 end
where city='上海'
UNION ALL
select city from base
where city<>'上海'注意别排序
declare @table table([city] varchar(4))
insert @table
select '北京' union all
select '大连' union all
select '上海' union all
select '天津' union all
select '上海' union all
select '大连'select * from @table
order by case [city] when '上海' then 0 else 1 end
-----------------------
上海
上海
大连
天津
北京
大连
declare @table table([city] varchar(4))
insert @table
select '北京' union all
select '大连' union all
select '上海' union all
select '天津' union all
select '上海' union all
select '大连'select * from @table
order by case [city] when '上海' then 'A' else [city] end
---------------------
上海
上海
北京
大连
大连
天津
from tb
order by case [city] when '上海' then 'A' else [city] end
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-10 09:31:37
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(4))
insert [tb]
select '北京' union all
select '大连' union all
select '上海' union all
select '天津' union all
select '上海' union all
select '大连'
--------------开始查询--------------------------
select
*
from
tb
order by
case col when '上海' then 0 else 1 end----------------结果----------------------------
/*col
----
上海
上海
大连
天津
北京
大连(6 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-10 09:31:37
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(4))
insert [tb]
select '北京' union all
select '大连' union all
select '上海' union all
select '天津' union all
select '上海' union all
select '大连'
--------------开始查询--------------------------
select
*
from
tb
order by
case col when '上海' then 'a' else col end----------------结果----------------------------
/*col
----
上海
上海
北京
大连
大连
天津(6 行受影响)
*/
CREATE TABLE BASE
(
CITY VARCHAR(8)
)
GOINSERT INTO BASE
SELECT '北京' UNION ALL
SELECT '大连' UNION ALL
SELECT '上海' UNION ALL
SELECT '天津' UNION ALL
SELECT '上海' UNION ALL
SELECT '大连'
GOSELECT * FROM BASE ORDER BY CHARINDEX(CITY,'上海北京天津大连')
这招狠! 哈哈哈!
delete from #temp
declare @city varchar(10),@city1 varchar(10)
--create table #temp(city varchar(10))
declare yb1 scroll cursor for select city from base
open yb1
fetch relative 3 from yb1 into @city
select @city1=@city
insert into #temp select @city1
fetch relative 2 from yb1 into @city
select @city1=@city
insert into #temp select @city1
fetch relative -4 from yb1 into @city
select @city1=@city
insert into #temp select @city1
fetch relative 1 from yb1 into @city
select @city1=@city
insert into #temp select @city1
fetch relative 2 from yb1 into @city
select @city1=@city
insert into #temp select @city1
fetch relative 2 from yb1 into @city
select @city1=@city
insert into #temp select @city1
select * from #temp
close yb1
deallocate yb1
select city from base order by case '上海' when 0 else 1 end