会者不难的问题
表一,
bh name
3513100278 刘得化
...
...
表二,
bh rq mc
35131002771997-05-08 00:00:0030291
35131002781988-11-28 00:00:004001
35131002781997-08-06 00:00:005213
35131003921994-12-28 00:00:005104V
35131006191996-07-02 00:00:004002
35131006221996-04-17 00:00:004002
35131006301994-12-27 00:00:003032
35131006411996-09-14 00:00:003003
35131006691997-12-24 00:00:003003
...
...
想得到这样的结果;
bh name mc
351310028 刘得化 4001,5123
如果类似
select bh, name, (select mc from 表二 as T2 where T2.BH=T1.BH) from 表一 as T1
实现不了,也可以
select bh, name, dbo.func(bh) from 表一
当然 dbo.func() 函数中不要用while 这样的东东,最好是一条语句.
先谢谢大家了.
[解决办法]
if object_id( 'tbTest1 ') is not null
drop table tbTest1
if object_id( 'tbTest2 ') is not null
drop table tbTest2
if object_id( 'fnMerge ') is not null
drop function fnMerge
GO
create table tbTest1(bh varchar(10),name varchar(10))
insert tbTest1
select '3513100278 ', '刘得化 '
create table tbTest2(bh varchar(10),rq datetime,mc varchar(10))
insert tbTest2
select '3513100277 ', '1997-05-08 00:00:00 ', '30291 ' union all
select '3513100278 ', '1988-11-28 00:00:00 ', '4001 ' union all
select '3513100278 ', '1997-08-06 00:00:00 ', '5213 ' union all
select '3513100392 ', '1994-12-28 00:00:00 ', '5104V ' union all
select '3513100619 ', '1996-07-02 00:00:00 ', '4002 ' union all
select '3513100622 ', '1996-04-17 00:00:00 ', '4002 ' union all
select '3513100630 ', '1994-12-27 00:00:00 ', '3032 ' union all
select '3513100641 ', '1996-09-14 00:00:00 ', '3003 ' union all
select '3513100669 ', '1997-12-24 00:00:00 ', '3003 '
GO
create function fnMerge(@bh varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + mc from tbTest2 where bh = @bh
return stuff(@str,1,1, ' ')
end
GO
----查询
select *,dbo.fnMerge(bh) as mc from tbTest1
drop table tbTest1,tbTest2
drop function fnMerge
/*结果
3513100278 刘得化 4001,5213
*/
[解决办法]
create table T1(bh bigint,name varchar(20))
insert T1 select 3513100278, '刘得化 '
create table T2(bh bigint,rq datetime,mc varchar(20))
insert T2 select 3513100277, '1997-05-08 00:00:00 ', '30291 '
union all select 3513100278, '1988-11-28 00:00:00 ', '4001 '
union all select 3513100278, '1997-08-06 00:00:00 ', '5213 '
union all select 3513100392, '1994-12-28 00:00:00 ', '5104V '
union all select 3513100619, '1996-07-02 00:00:00 ', '4002 '
union all select 3513100622, '1996-04-17 00:00:00 ', '4002 '
union all select 3513100630, '1994-12-27 00:00:00 ', '3032 '
union all select 3513100641, '1996-09-14 00:00:00 ', '3003 '
union all select 3513100669, '1997-12-24 00:00:00 ', '3003 '
select * from T1
select * from T2
create function dbo.fnmerge(@bh bigint)
returns varchar(8000)
as
begin
declare @mc varchar(8000)
set @mc= ' '
select @mc=@mc+ ', ' +[mc] from T2 where bh=@bh
return stuff(@mc,1,1, ' ')
end
go
select a.bh,a.name,b.rq,b.mc from T1 a inner join
(select bh,rq,dbo.fnmerge(bh) as mc from T2 group by bh,rq) b on a.bh=b.bh
[解决办法]
create function fno(@bh varchar(10) )
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql + ', '+ mc from tablename where bh=@bh
return right(@sql,len(@sql)-1)
end
select a.bh,a.name,b.rq,b.mc from T1 a inner join
(select bh,rq,dbo.fno(bh) as mc from T2 group by bh,rq) b on a.bh=b.bh
