表a
id_a id_b value_a
1 001 a
1 002 b
1 003 c
2 006 d
2 007 e表b
id_b value_b
001 cc
002 dd
003 ee
004 ff
006 gg
007 fff通过sql 语句,要这样的结果:
1 a b c 001 cc 002 dd 003 ee
即,与id_a 为1 相关的内容都显示在同一行,sql语句怎么写????
id_a id_b value_a
1 001 a
1 002 b
1 003 c
2 006 d
2 007 e表b
id_b value_b
001 cc
002 dd
003 ee
004 ff
006 gg
007 fff通过sql 语句,要这样的结果:
1 a b c 001 cc 002 dd 003 ee
即,与id_a 为1 相关的内容都显示在同一行,sql语句怎么写????
select 表a.* ,表B.value_b from 表a,表b where 表a.id_b=表b.id_b
declare @value_a varchar(100)
set @value_a=''
declare @value_b varchar(100)
set @value_b=''
declare @id_b varchar(100)
set @id_b=''select @value_a=@value_a+' '+tt.value_a, @id_b=id_b, @value_b=@id_b+' '+@value_b+value_b
from (select 表a.* ,表B.value_b from 表a,表b where 表a.id_b=表b.id_b )tt
where tt.id_a=1
print @value+' '+@value_b
我看不明明白,能否解释一下,,有没有更简单一些的呢?
create table 表a (id_a int, id_b varchar(10), value_a varchar(20))
insert into 表a select 1, '001', 'a'
insert into 表a select 1, '002', 'b'
insert into 表a select 1, '003', 'c'
insert into 表a select 2, '006', 'd'
insert into 表a select 2, '007', 'e'create table 表b (id_b int, value_b varchar(20))
insert into 表b select 001, 'cc'
insert into 表b select 002, 'dd'
insert into 表b select 003, 'ee'
insert into 表b select 004, 'ff'
insert into 表b select 006, 'gg'
insert into 表b select 007, 'ff'
create procedure txt (@id int)
asdeclare @value_a varchar(100)
set @value_a=''
declare @value_b varchar(100)
set @value_b=''
declare @id_b varchar(100)
set @id_b=''select @value_a=@value_a+' '+tt.value_a, @id_b=id_b, @value_b=@id_b+' '+value_b+' '+@value_b
from (select 表a.* ,表B.value_b from 表a,表b where 表a.id_b=表b.id_b )tt
where tt.id_a=@id
print Convert(varchar(10),@id)+' '+@value_a+' '+@value_bexec txt 1
结果如下:
1 a b c 003 ee 002 dd 001 cc
select 表a.* ,表B.value_b from 表a,表b where 表a.id_b=表b.id_b
在设三个字符 变量 @value_a,@value_b, @id_b 分别接受 value_a,value_b,id_b 的值
通过
select @value_a=@value_a+' '+tt.value_a, @id_b=id_b, @value_b=@id_b+' '+value_b+' '+@value_b
from (select 表a.* ,表B.value_b from 表a,表b where 表a.id_b=表b.id_b )tt
where tt.id_a=@id
就可以达到累加效果,
最后在把这三个相加 就可以得到你想要的效果了
set @value_a=''
declare @value_b varchar(100)
set @value_b=''
declare @id_b varchar(100)
set @id_b=''select @value_a=@value_a+' '+tt.value_a, @id_b=id_b, @value_b=@id_b+' '+value_b+' '+@value_b
from (select 表a.* ,表B.value_b from 表a,表b where 表a.id_b=表b.id_b )tt
where tt.id_a=1
print Convert(varchar(10),1)+' '+@value_a+' '+@value_b大概也是一样的,不过楼主可能说的是要用一条SQL语句的话!!我就不会了
还望他高人帮忙
如果不考虑结果的先后关系,
只需声明一个变量
declare @a table(id_a int, id_b char(3), value_a varchar(20))
insert into @a select 1, '001', 'a'
insert into @a select 1, '002', 'b'
insert into @a select 1, '003', 'c'
insert into @a select 2, '006', 'd'
insert into @a select 2, '007', 'e'declare @b table(id_b char(3), value_b varchar(20))
insert into @b select '001', 'cc'
insert into @b select '002', 'dd'
insert into @b select '003', 'ee'
insert into @b select '004', 'ff'
insert into @b select '006', 'gg'
insert into @b select '007', 'ff'declare @sStr varchar(2000)
set @sStr=''
select @sStr=@sStr+' '+a.id_b+' '+value_a+' '+value_b from @a a ,@b b
where a.id_b=b.id_b and id_a=1--(条件)select @sStr
/*
-------------------------------------------------------------
001 a cc 002 b dd 003 c ee
*/