急求一条sql语句,找一条链子出来的!该如何处理
急求一条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 '
一个表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 '