select * from a order by charindex(city,'北京上海深圳'),ID
select * from tb order by case 城市 when '北京' then 1 when'上海' then 2 when '深圳' then 3 end,id
或者 select * from tb order by charindex(city,'北京上海深圳'),id
select * from a order by charindex(city,'北京上海深圳'),ID
SELECT * FROM TB ORDER BY CHARINDEX (city,'北京,上海,深圳'),ID
select * from tb order by case 城市 when '北京' then 1 when'上海' then 2 when '深圳' then 3 end,id
-- ========================================= -- -----------t_mac 小编------------------- --------------------希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb( ID int, city varchar(10)) go insert tb SELECT 1,'a' insert tb SELECT 2,'b' insert tb SELECT 3,'kp' insert tb SELECT 4,'k' insert tb SELECT 5,'上海' insert tb SELECT 6,'d' insert tb SELECT 7,'深圳' insert tb SELECT 9,'北京' go declare @n int set @n=(select COUNT(*) from tb)/2-1 set rowcount @n select * into #1 from tb where city<>'深圳' order by case city when '北京' then 1 else 2 end,id set rowcount 0select * into #2 from tb where city<>'上海' and ID not in(select id from #1 ) order by case city when '深圳' then 2 else 1 end ,ID select * from #1 union all select * from tb where city='上海' union all select * from #2 go /* ID city ----------- ---------- 9 北京 1 a 2 b 5 上海 3 kp 4 k 6 d 7 深圳(8 行受影响) */ go
同小Fselect * from tb order by case 城市 when '深圳' then 1 when'上海' then 2 when '北京' then 3 end desc,id asc
select * from tb order by case 城市 when '北京' then 1 when'上海' then 2 when '深圳' then 3 end,id
select * from a order by charindex(city,'北京上海深圳'),ID
正解都出来了,郁闷…… select * from a order by charindex(city,'北京上海深圳'),ID
select * from tb order by charindex(city,'北京上海深圳'),id
为什么每个人都觉得是select * from tb order by charindex(city,'北京上海深圳'),id这样排出来 是三个连在一起的。我觉得楼主的意思 应该是三个排开阿支持下8楼的自己
select * from tb order by charindex(city,''),id 这个语句出来的结果和select * from tb order by id一样,应该是不对的,就算是三个连在一起也要用那个case语句。你的理解和我的一样,应该是3个不在一起的,我是这样认为的,不知道楼主的意思。。支持你。
支持8楼. if object_id('test') is not null drop table testcreate table test ( id int identity(1,1), city varchar(20), cotent varchar(100) )insert test select '上海','' union all select '北京','' union all select '河北','' union all select '河南','' union all select '深圳','' union all select '香港','' union all select '山东','' select * from test order by charindex(city,'北京上海深圳'),idselect * from test order by case city when '北京' then 1 when'上海' then 2 when '深圳' then 3 end,id /* id city cotent ----------- -------------------- ---------------------------------------------------------------------------------------------------- 3 河北 4 河南 6 香港 7 山东 2 北京 1 上海 5 深圳 (所影响的行数为 7 行)**/
select *,case case when 城市='北京' then -3 when 城市='上海' then -2 when 城市='深圳' then -1 ELSE [ID] END AS sx
*
from
tb
order by
case 城市 when '北京' then 1
when'上海' then 2
when '深圳' then 3
end,id
select * from tb order by charindex(city,'北京上海深圳'),id
case 城市 when '北京' then 1
when'上海' then 2
when '深圳' then 3
end,id
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( ID int, city varchar(10))
go
insert tb SELECT 1,'a'
insert tb SELECT 2,'b'
insert tb SELECT 3,'kp'
insert tb SELECT 4,'k'
insert tb SELECT 5,'上海'
insert tb SELECT 6,'d'
insert tb SELECT 7,'深圳'
insert tb SELECT 9,'北京'
go
declare @n int
set @n=(select COUNT(*) from tb)/2-1
set rowcount @n
select *
into #1
from tb
where city<>'深圳'
order by case city when '北京' then 1 else 2 end,id
set rowcount 0select * into #2
from tb where city<>'上海' and ID not in(select id from #1 )
order by case city when '深圳' then 2
else 1 end ,ID
select * from #1
union all
select *
from tb where city='上海'
union all
select * from #2
go
/*
ID city
----------- ----------
9 北京
1 a
2 b
5 上海
3 kp
4 k
6 d
7 深圳(8 行受影响)
*/
go
*
from
tb
order by
case 城市 when '深圳' then 1
when'上海' then 2
when '北京' then 3
end desc,id asc
order by
case 城市 when '北京' then 1
when'上海' then 2
when '深圳' then 3
end,id
select * from a order by charindex(city,'北京上海深圳'),ID
select * from a order by charindex(city,'北京上海深圳'),ID
这个语句出来的结果和select * from tb order by id一样,应该是不对的,就算是三个连在一起也要用那个case语句。你的理解和我的一样,应该是3个不在一起的,我是这样认为的,不知道楼主的意思。。支持你。
if object_id('test') is not null
drop table testcreate table test
(
id int identity(1,1),
city varchar(20),
cotent varchar(100)
)insert test
select '上海','' union all
select '北京','' union all
select '河北','' union all
select '河南','' union all
select '深圳','' union all
select '香港','' union all
select '山东',''
select * from test order by charindex(city,'北京上海深圳'),idselect
*
from
test
order by
case city when '北京' then 1
when'上海' then 2
when '深圳' then 3
end,id
/*
id city cotent
----------- -------------------- ----------------------------------------------------------------------------------------------------
3 河北
4 河南
6 香港
7 山东
2 北京
1 上海
5 深圳 (所影响的行数为 7 行)**/
when 城市='上海' then -2
when 城市='深圳' then -1
ELSE [ID] END AS sx
from tb
order by
sx