如何查询出以很不爽的结果？顶者有分。。。。。。。

[hbjmdx008]

以前数据库设计不合理，现在要把
ID Order
1 01
2 0101
3 0102
4 010101
5 01010101


现在要查出类似ID为5的所有上级ID(上级ID就像2是4 和5的上级ID)
print @values
结果要变成这样

1,2,4

说明：ID5的order 长度是不定的，但最长为18位。
希望高手帮助。。

[子陌红尘]

declare @values varchar(100)

select @values=isnull(@values,'')+','+rtrim(ID) from 表 a,表 b where b.Order like a.Order+'%' and b.ID=5

print @values

[hrb2008]

select * from table where charindex(','+Order,','+'01010101')>0

[子陌红尘]

declare @t table(ID int,[Order] varchar(18))
insert into @t select 1,'01'
insert into @t select 2,'0101'
insert into @t select 3,'0102'
insert into @t select 4,'010101'
insert into @t select 5,'01010101'


declare @values varchar(100)

select @values=isnull(@values,'')+','+rtrim(a.ID) from @t a,@t b where b.[Order] like a.[Order]+'%' and a.ID!=b.ID and b.ID=5

set @values=stuff(@values,1,1,'')

print @values

[hbjmdx008]

谢谢，先试一下。

[hrb2008]

CREATE TABLE tbl
(
ID int,
[Order] varchar(10)
)
INSERT INTO tbl
SELECT 1,'01'UNION ALL
SELECT 2,'0101'UNION ALL
SELECT 3,'0102'UNION ALL
SELECT 4,'010101'UNION ALL
SELECT 5,'01010101'
SELECT * FROM tbl WHERE charindex(','+[Order],','+'01010101')>0 AND LEN([Order])<LEN('01010101')
--
ID Order
----------- ----------
1 01
2 0101
4 010101

(3 行受影响)

[zlp321002]

--参考下2楼的

declare @t table(ID int,[Order] varchar(18))
insert into @t select 1,'01'
insert into @t select 2,'0101'
insert into @t select 3,'0102'
insert into @t select 4,'010101'
insert into @t select 5,'01010101'

declare @id varchar(200)
set @id=''
select @id=@id+','+ltrim(id) from
(
select id from @t where charindex(','+[Order],','+'01010101')>0 and ID!=5
) tb
print stuff(@id,1,1,'')

--
1,2,4

[w75251455]

create table table1(ID int,[Order] varchar(18))
insert into table1 select 1,'01'
insert into table1 select 2,'0101'
insert into table1 select 3,'0102'
insert into table1 select 4,'010101'
insert into table1 select 5,'01010101'

select * from table1
where charindex([Order],(select [Order] from table1 where id = 5))=1--这里为了避免一些问题最好是=1

[w75251455]

select * from table1
where charindex([Order],(select [Order] from table1 where id = 5))=1
and [Order]<>(select [Order] from table1 where id = 5)