父表:id name age
1 aa1 20
2 aa2 21
子表:id parent_id english_score math_score
1 1 80 87
2 1 98 95
3 2 71 65
我想两张表查询,得到的结果是:
id name age score
1 aa1 20 80,87,98,95
2 aa2 21 71,65先谢谢各位了,看有没有好的办法。
1 aa1 20
2 aa2 21
子表:id parent_id english_score math_score
1 1 80 87
2 1 98 95
3 2 71 65
我想两张表查询,得到的结果是:
id name age score
1 aa1 20 80,87,98,95
2 aa2 21 71,65先谢谢各位了,看有没有好的办法。
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO -- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id drop table tb
drop function dbo.f_str /*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
你这是sqlserver的写法吧!
create or replace function sumscore(sunid integer)
return varchar2
as
scores varchar2(50);
begin
for a in (select s.english_score||','||s.math_score||',' as da from test_sun s where s.parent_id=sunid)
loop
scores := a.da||scores;
end loop;
return scores;
end sumscore;然后你一调用:
select t.id,t.name,t.age,sumscore(t.id) from test_father t
不就OK了?