求sql查询语句

Question

id     sb_1     sb_2
5       23       24
6       25       24
7       24       32
8       32       26
9       26       33把相关的数据按一个记录查出来
如下
23  24  32  26  33
25  24  32  26  33

Answer 1

类似路径问题
create table tb1(UserID int, FriendID int)
insert into tb1 
select 1,2
union all 
select 1,3
union all 
select 3,5
union all 
select 5,2declare @int int
set @int = 1select @int'phint',
'1-'+cast((select userid from(select UserID union all select friendid)a where userid<>1)as varchar(10))'ph'
into #tb2
from tb1 where userid =1 or friendid=1while(@@rowcount>0)
begin
set @int=@int+1
insert into #tb2 
select @int,* from(
select ph+'-'+(
case when RIGHT(tb2.ph,1)=userid then cast(friendid as varchar(10))
when CHARINDEX(cast(userid as varchar(10)),ph)=0 then cast(userid as varchar(10)) end)ph
from (select * from #tb2 where phint=@int-1 and RIGHT(ph,1)<>5)tb2 left join tb1 
on tb1.userid=RIGHT(tb2.ph,1) or tb1.friendid=RIGHT(tb2.ph,1)
)a
where CHARINDEX(RIGHT(ph,1),ph)=len(ph) and ph is not null
endselect * from #tb2 where substring(ph,len(ph),1)=5
drop table #tb2

Answer 2

create table xyz(id int, sb_1 int, sb_2 int)
insert xyz select 5 ,23 ,24
union all select 6 ,25, 24
union all select 7 ,24, 32
union all select 8 ,32, 26
union all select 9 ,26, 33
go
create function get_xyz(@sb int)
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=ltrim(@sb)
while exists(select sb_2 from xyz where sb_1=@sb)
begin
select @sb=sb_2 from xyz where sb_1=@sb
select @a=@a+' '+ltrim(sb_1) from xyz where sb_1=@sb
end
return @a+' '+ltrim(@sb)
end
go
select id,sb_1,sb_2, dbo.get_xyz(sb_1) result from xyz

Answer 3

数据表就是那,如何用SQL语句查询

Answer 4

创建函数，然后运行查询
--result
/*
id          sb_1        sb_2        result                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------- ----------- ----------- -------------------- 
5           23          24          23 24 32 26 33
6           25          24          25 24 32 26 33
7           24          32          24 32 26 33
8           32          26          32 26 33
9           26          33          26 33（所影响的行数为 5 行）*/

Answer 5

如果不要所有的id都出现则：
select id,sb_1,sb_2, dbo.get_xyz(sb_1) result from xyz a where not exists(select 1 from xyz where sb_2=a.sb_1)
--result
/*
id          sb_1        sb_2        result                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------- ----------- ----------- ----------------
5           23          24          23 24 32 26 33
6           25          24          25 24 32 26 33（所影响的行数为 2 行）*/

Answer 6

首先创建一个函数
create function get_xyz(@sb int)
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=ltrim(@sb)
while exists(select sb_2 from xyz where sb_1=@sb)
begin
select @sb=sb_2 from xyz where sb_1=@sb
select @a=@a+' '+ltrim(sb_1) from xyz where sb_1=@sb
end
return @a+' '+ltrim(@sb)
end
再创建视图:
create view vw_get
as
select id,sb_1,sb_2, dbo.get_xyz(sb_1) result from xyz a where not exists(select 1 from xyz where sb_2=a.sb_1)go
--查询:
select * from vw_get
--result
/*
id sb_1 sb_2 result
----------- ----------- ----------- ----------------
5 23 24 23 24 32 26 33
6 25 24 25 24 32 26 33（所影响的行数为 2 行）*/

Answer 7

函数里的表名xyz是不是的换成我的表名啊?

Answer 8

我的数据库表名为dtline
字段名为line_id,line_porta,line_portb里面的数据就是类似上面的数据,该则么弄啊,非常谢谢