有两张表,一张主表,一张明晰,
主表id value 1 ‘2’
2 ‘ss’
3 ‘dd’
明晰表主表id,infos --主表id对应上面主表的id
1 '11'
1 '12'
1 '13'
2 '12'能不能用一条sql得到数据:
id value infos
1 2 11-12-13这样,怎么写啊?
主表id value 1 ‘2’
2 ‘ss’
3 ‘dd’
明晰表主表id,infos --主表id对应上面主表的id
1 '11'
1 '12'
1 '13'
2 '12'能不能用一条sql得到数据:
id value infos
1 2 11-12-13这样,怎么写啊?
,STUFF(SELECT '-'+LTRIM(infos) FROM 明晰表 B WHERE A.ID=B.ID FOR XML PATH('')),1,1,'')
FROM 主表 A
WHERE id=1
returns varchar(1000)
as
begin
declare @info varchar(1000)
set @info = ''
select @info = @info + infos from 明细表
return @info
endselect id,fun_test(id) from 明细表
group by id类似的
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([id] int,[value] varchar(2))
insert [t1]
select 1,'2' union all
select 2,'ss' union all
select 3,'dd'
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([id] int,[infos] int)
insert [t2]
select 1,11 union all
select 1,12 union all
select 1,13 union all
select 2,12
---查询---
select
a.id,
a.value,
stuff((select '-'+ltrim(infos) from t2 where t2.id=a.id for xml path('')),1,1,'')
from
t1 a,t2 b
where a.id=b.id
and a.id=1
group by a.id,a.value---结果---
id value
----------- ----- -------------------
1 2 11-12-13(1 行受影响)
declare
v_string varchar(200);
cursor outer_cursor is select id,value from tb1;
cursor inter_cursor(p_id in tb1.id%type) is select infos from tb2 where id=p_id;
begin
for outercursor in outer_cursor loop
v_string:=to_char(outercursor.id)||outercursor.value;
for incursor in inter_cursor loop
v_string:=v_string||incursor.infos;
end loop;
dbms_output.put_line(v_string);
end loop;
end;
create table tb1(id int, value varchar(10))
insert into tb1 values(1 , '2')
insert into tb1 values(2 , 'ss')
insert into tb1 values(3 , 'dd')
create table tb2(id int,infos varchar(10))
insert into tb2 values(1 , '11')
insert into tb2 values(1 , '12')
insert into tb2 values(1 , '13')
insert into tb2 values(2 , '12')
go
create function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + '-' , '') + cast(infos as varchar) from tb2 where id = @id
return @str
end
go--调用函数
select m.id , m.value , infos = dbo.f_str(m.id) from tb1 m , tb2 n where m.id = n.id group by m.id , m.valuedrop function dbo.f_str
drop table tb1 , tb2/*
id value infos
----------- ---------- -----------
1 2 11-12-13
2 ss 12(所影响的行数为 2 行)
*/
insert into tb1 values(1 , '2')
insert into tb1 values(2 , 'ss')
insert into tb1 values(3 , 'dd')
create table tb2(id int,infos varchar(10))
insert into tb2 values(1 , '11')
insert into tb2 values(1 , '12')
insert into tb2 values(1 , '13')
insert into tb2 values(2 , '12')
go
select m.* , n.infos from tb1 m,
(
select id, infos = stuff((select '-' + infos from tb2 t where id = tb2.id for xml path('')) , 1 , 1 , '')
from tb2
group by id
) n
where m.id = n.iddrop table tb1 , tb2/*
id value infos
----------- ---------- -----------
1 2 11-12-13
2 ss 12(2 行受影响)
*/