SQL 排序
小弟做了一个数据库，里面有以S，Z，I开头的三种单据，现在我要把它在ASP中分页显示出来，这三个单据都是通过另外一个关键字联系起来的，我想出来的结果是先输出S单据，然后是Z单据，最后是I单据
如下：

S-06-001   ALA060032
Z-07-032   AlA060032
I-07-032   ALA060032
I-07-033   ALA060032

S-06-002   ALA060033
Z-07-030   AlA060033
I-07-034   ALA060033
……
这样如何实现，请高手指教！


------解决方案--------------------
select *
from 表
order by case left(单据编号,1) when 'S ' then 1 when 'Z ' then 2 when 'I ' then 3 end

------解决方案--------------------

wangtiecheng能否帮我解释以下,这两句话中的1为什么不一样?前者是数字1,后者是指第一列?
--建立测试环境
create table #tb(sid varchar(20),col varchar(10))
insert #tb(sid,col)
select 'S-06-001 ', 'ALA060032 ' union all
select 'Z-07-032 ', 'AlA060032 ' union all
select 'I-07-032 ', 'ALA060032 ' union all
select 'I-07-033 ', 'ALA060032 ' union all
select 'S-06-002 ', 'ALA060033 ' union all
select 'Z-07-030 ', 'AlA060033 ' union all
select 'I-07-034 ', 'ALA060033 '
go
--执行测试语句
select t.sid,t.col from #tb t order by case left(sid,1) when 'S ' then 1 when 'Z ' then 2 when 'I ' then 3 end
select t.sid,t.col from #tb t order by 1
--删除测试环境
drop table #tb
go
/*--测试结果
sid col
-------------------- ----------
S-06-001 ALA060032
S-06-002 ALA060033
Z-07-030 AlA060033
Z-07-032 AlA060032
I-07-032 ALA060032
I-07-033 ALA060032
I-07-034 ALA060033

(7 row(s) affected)

sid col
-------------------- ----------
S-06-001 ALA060032
S-06-002 ALA060033
Z-07-030 AlA060033
Z-07-032 AlA060032
I-07-032 ALA060032
I-07-033 ALA060032
I-07-034 ALA060033

(7 row(s) affected)

sid col
-------------------- ----------
I-07-032 ALA060032
I-07-033 ALA060032
I-07-034 ALA060033
S-06-001 ALA060032
S-06-002 ALA060033
Z-07-030 AlA060033
Z-07-032 AlA060032

(7 row(s) affected)
*/


------解决方案--------------------
--建立测试环境
create table #tb(sid varchar(20),col varchar(10))
insert #tb(sid,col)
select 'S-06-001 ', 'ALA060032 ' union all
select 'Z-07-032 ', 'AlA060032 ' union all
select 'I-07-032 ', 'ALA060032 ' union all
select 'I-07-033 ', 'ALA060032 ' union all
select 'S-06-002 ', 'ALA060033 ' union all
select 'Z-07-030 ', 'AlA060033 ' union all
select 'I-07-034 ', 'ALA060033 '
go
--执行测试语句
select t.sid,t.col from #tb t order by t.col,case left(sid,1) when 'S ' then 1 when 'Z ' then 2 when 'I ' then 3 end

--删除测试环境
drop table #tb
go


--测试结果
S-06-001 ALA060032
Z-07-032 AlA060032
I-07-032 ALA060032
I-07-033 ALA060032
S-06-002 ALA060033
Z-07-030 AlA060033
I-07-034 ALA060033