取巧一下多弄一个排序列出来: select case when LID=0 then ID else LID end as HID, ID, LID, NAME into #tmp from AAselect * from #tmp order by HID, ID, LID
原来case when 可以直接用在order by子句,受教咯。Thanks.
libin_ftsafe(子陌红尘:当libin告别ftsafe) 你的(case LID when 0 then ID else LID end),ID order by id,id怎么不会有问题,能否解释下。
因为楼主没说明是两层,我当作多层处理--函数 create function fn_order( @id int ) returns @r table ( ID int, LID int, NAME varchar(30), OrderCode varchar(200) ) as begin insert @r select *,right('00000'+cast(id as varchar(10)),5) from aa where id=@id while exists ( select 1 from aa where id not in (select id from @r) and Lid in (select id from @r) ) insert @r select a.*,t.ordercode+right('00000'+cast(a.id as varchar(10)),5) from aa a,@r t where a.Lid=t.id and a.id not in (select id from @r) return end go--调用select id,lid,name from dbo.fn_order(0) order by ordercode
好象这样就可以 select * from ck order by (case LID when 0 then id else lid end) 后面接个,id更难理解!
jaway(无限渴望) : 如果两层,libin_ftsafe(子陌红尘:当libin告别ftsafe) 的是对的 如果只order by (case LID when 0 then id else lid end) 可能(前三条次序):
select case when LID=0 then ID else LID end as HID, ID, LID, NAME
into #tmp
from AAselect * from #tmp order by HID, ID, LID
你的(case LID when 0 then ID else LID end),ID
order by id,id怎么不会有问题,能否解释下。
create function fn_order(
@id int
)
returns @r table (
ID int,
LID int,
NAME varchar(30),
OrderCode varchar(200)
)
as
begin
insert @r select *,right('00000'+cast(id as varchar(10)),5) from aa where id=@id
while exists (
select 1 from aa
where id not in (select id from @r)
and Lid in (select id from @r)
)
insert @r
select a.*,t.ordercode+right('00000'+cast(a.id as varchar(10)),5)
from aa a,@r t
where a.Lid=t.id
and a.id not in (select id from @r)
return
end
go--调用select id,lid,name
from dbo.fn_order(0)
order by ordercode
select
*
from
ck
order by
(case LID when 0 then id else lid end)
后面接个,id更难理解!
如果两层,libin_ftsafe(子陌红尘:当libin告别ftsafe) 的是对的
如果只order by
(case LID when 0 then id else lid end)
可能(前三条次序):
11 0 A仓库
69 11 货架00004
17 11 货架00001
也可能
17 11 货架00001
69 11 货架00004
11 0 A仓库