急求一条sql语句,找一条链子出来的!!
一个表table1:
id strA1 strA2 strB1 strB2 status
1 1 2 2 3 t1
2 2 3 5 4 t1
3 1 5 2 4 t2
4 5 4 2 6 t1
............
要的结果是,当条件status=t1时,要找出一条链子出来:
strA1,strA2(1,2)链接的是strB1,strB2(2,3)
然后找出strA1,strA2是(2,3)的链接的是(5,4)
这样一直对应下去.
上面的结果链就是
1(第一层) (1,2)
2(第二层) (2,3)
3(第三层) (5,4)
4(第四层) (2,6)
怎么样写sql语句??
[解决办法]
declare @test table (id int, strA1 varchar(10), strA2 varchar(10), strB1 varchar(10), strB2 varchar(10), status varchar(10))
insert @test
select '1 ', '1 ', '2 ', '2 ', '3 ', 't1 ' union all
select '2 ', '2 ', '3 ', '5 ', '4 ', 't1 ' union all
select '3 ', '1 ', '5 ', '2 ', '4 ', 't2 ' union all
select '4 ', '5 ', '4 ', '2 ', '6 ', 't1 '
select * from @test
select strA1, strA2 from @test where status = 't1 ' and strA1 + strA2 not in (select strB1 + strB2 from @test where status = 't1 ')
union all
select strB1, strB2 from @test where status = 't1 '
[解决办法]
至少需要个辅助函数...
[解决办法]
create table t
(id int, strA1 int, strA2 int, strB1 int, strB2 int, status varchar(10))
insert into t
select 1, 1, 2, 2, 3 , 't1 ' union all
select 2, 2, 3, 5, 4 , 't1 ' union all
select 3, 1, 5, 2, 4, 't2 ' union all
select 4, 5, 4, 2, 6, 't1 '
select cast(strA1 as varchar(10))+ ', '+cast(strA2 as varchar(10))as name from t where status= 't1 '
union
select cast(strB1 as varchar(10))+ ', '+cast(strB2 as varchar(10))as name from t where status= 't1 '
name
---------------------
1,2
2,3
2,6
5,4
(4 row(s) affected)
[解决办法]
drop table test3
go
create table test3 (id int, strA1 varchar(10), strA2 varchar(10), strB1 varchar(10), strB2 varchar(10), status varchar(10))
go
insert into test3
select '1 ', '1 ', '2 ', '2 ', '3 ', 't1 '
union all
select '2 ', '2 ', '3 ', '5 ', '4 ', 't1 '
union all
select '3 ', '1 ', '5 ', '2 ', '4 ', 't2 '
union all
select '4 ', '5 ', '4 ', '2 ', '6 ', 't1 '
select * from test3
select * from test3 t
where status= 't1 '
and exists
(
select * from
(
select strA1,strA2 from test3
union all
select strB1,strB2 from test3
) x
group by strA1,strA2
having count(1) <=2
and status=t.status
and ((strA1=t.strA1 and strA2=t.strA2) or (strA1=t.strA2 and strB1=t.strB2))
)
[解决办法]
create table table1
(id int, strA1 int, strA2 int, strB1 int, strB2 int, status varchar(10))
insert into table1
select 1, 1, 2, 2, 3 , 't1 ' union all
select 2, 2, 3, 5, 4 , 't1 ' union all
select 3, 1, 5, 2, 4, 't2 ' union all
select 4, 5, 4, 2, 6, 't1 '
alter Function D_Tree(@tstatus varchar(1000))
Returns @Tree Table(id int,str1 int,str2 int)
As
Begin
delete from @Tree
Insert @Tree Select top 1 id,strA1,strA2 From table1 Where status=@tstatus
Insert @Tree Select a.id,a.strB1,a.strB2 From table1 a,@Tree b Where a.id=b.id and a.strA1=b.str1 and a.strA2=b.str2
While @@Rowcount > 0
Insert @Tree Select A.id,A.StrB1,A.StrB2 From table1 A,@Tree B
where A.strA1 = B.str1 and A.strA2=B.str2 And A.id Not In (Select id From @Tree)
Return
End
GO
select * from dbo.D_Tree( 't1 ')
