select distinct zone from priceInfo order by charindex('一厂二厂三厂',zone)
--> --> (Roy)生成測試數據
declare @t table([Col1] nvarchar(2)) Insert @t select N'三厂' union all select N'一厂' union all select N'二厂'
Select [Col1] from @t group by [Col1] order by case [Col1] when N'一厂' then 1 when N'二厂' then 2 when N'三厂' then 3 end/* 一厂 二厂 三厂 */
--SORRY 修正一下:---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-11-01 08:57:11 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[priceInfo] if object_id('[priceInfo]') is not null drop table [priceInfo] go create table [priceInfo]([zone] varchar(4)) insert [priceInfo] select '三厂' union all select '一厂' union all select '二厂' --------------开始查询-------------------------- select zone from priceInfo order by charindex(zone,'一厂二厂三厂') ----------------结果---------------------------- /* zone ---- 一厂 二厂 三厂(3 行受影响) */
--不要用distinct 否则会报错select zone from priceInfo group by zone order by charindex(zone,'一厂二厂三厂')
select distinct zone from priceInfo group by zone chaindex(N'zone','一厂二厂三厂')
用了distinct,修改 select zone from priceinfo group by zone charindex(zone,'一厂二厂三厂')
declare @t table([Col1] nvarchar(2))
Insert @t
select N'三厂' union all
select N'一厂' union all
select N'二厂'
Select [Col1] from @t group by [Col1] order by case [Col1] when N'一厂' then 1 when N'二厂' then 2 when N'三厂' then 3 end/*
一厂
二厂
三厂
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-01 08:57:11
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[priceInfo]
if object_id('[priceInfo]') is not null drop table [priceInfo]
go
create table [priceInfo]([zone] varchar(4))
insert [priceInfo]
select '三厂' union all
select '一厂' union all
select '二厂'
--------------开始查询--------------------------
select zone from priceInfo order by charindex(zone,'一厂二厂三厂')
----------------结果----------------------------
/* zone
----
一厂
二厂
三厂(3 行受影响)
*/
group by zone chaindex(N'zone','一厂二厂三厂')
select zone from priceinfo
group by zone charindex(zone,'一厂二厂三厂')