在线求一条SQL语句

Question

案件主体表ajzt案件ID 案由
1        打架
2        打架
3        打架
-------------
当事人表dsr案件ID   当事人姓名(name)
1        张三
1        立嗣
1        王武
2        李留
2        扩五想得到如下的结果
案件ID  案由  当事人姓名(name)
1       打架  张三，立嗣，王武
2       打架  李留，扩五

Answer 1

---------------------------------------------------------------------
--生成测试数据
create table ajzt(ID1 int,name1 varchar(20))
insert into ajzt select 1,'打架'
insert into ajzt select 2,'打架'
insert into ajzt select 3,'打架'create table dsr(ID2 int,name2 varchar(20))
insert into dsr select 1,'张三'
insert into dsr select 1,'李四'
insert into dsr select 1,'王五'
insert into dsr select 2,'赵六'
insert into dsr select 2,'邓七'
go--创建用户定义函数
create function f_str(@ment int)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+','+name2 from dsr where ID2 = @ment
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
--执行
select distinct [案件ID]=a.id1,[案由]=a.name1,[当事人姓名]=dbo.f_str(b.id2) from ajzt a right join dsr b on a.id1=b.id2 
go--输出结果
/**/
--删除测试数据
drop function f_str
drop table ajzt
drop table dsr
go

Answer 2

函数--建立函数
create function fn_names(
@Id int
)
returns varchar(2000)
as
begin
  declare @r varchar(2000)
  set @r=''
  select @r=@r+','+name from dsr where 案件ID  =@id
  if @r<>''
    set @r=stuff(@r,1,1,'')
  return @r
end
go--调用
select a.案件ID,a.案由,dbo.fn_names(a.案件ID) as name
from ajzt a,dsr b
where a.案件ID=b.案件ID
group by a.案件ID,a.案由

Answer 3

if object_id('tb_ajzt') is not null
    drop table tb_ajzt
if object_id('tb_dsr') is not null
    drop table tb_dsr
GO
----创建测试数据
create table tb_ajzt(案件ID int,案由 varchar(100))
create table tb_dsr(案件ID int,[当事人姓名(name)] varchar(50))
insert tb_ajzt
select 1,        '打架' union all
select 2,        '打架' union all
select 3,        '打架'
insert tb_dsr
select 1,        '张三' union all
select 1,        '立嗣' union all
select 1,        '王武' union all
select 2,        '李留' union all
select 2,        '扩五'
GO
if object_id('fnJoinString') is not null
    drop function fnJoinString
GO
----创建字符连接函数
create function fnJoinString(@ID int)
returns varchar(8000)
as
begin
    declare @str varchar(8000)
    set @str = ''
    select @str = @str + ',' + [当事人姓名(name)] from tb_dsr where 案件ID = @ID
    return stuff(@str,1,1,'')
end
GO
----查询
select distinct a.案件ID,a.案由,dbo.fnJoinString(b.案件ID) as [当事人姓名(name)]
from tb_ajzt as a inner join tb_dsr as b on a.案件ID = b.案件ID----清除测试环境
drop table tb_ajzt,tb_dsr
drop function fnJoinString/*结果
案件ID    案由      当事人姓名(name)
-------------------------------------------------------
1         打架      张三,立嗣,王武
2         打架      李留,扩五
*/

Answer 4

--建立测试环境
create table ajzt(ID int,Info varchar(20))
insert into ajzt 
select 1,'打架' union all
select 2,'打架' union all
select 3,'打架'create table dsr(ID int,Name varchar(20))
insert into dsr 
select 1,'张三' union all
select 1,'李四' union all
select 1,'王五' union all
select 2,'赵六' union all
select 2,'邓七'--测试
--创建取字符串函数
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MoreName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MoreName]
go
create function MoreName(@iID int)
returns nvarchar(200)
as
begin
declare @vReturnStr nvarchar(200)
set @vReturnStr=''
select @vReturnStr=@vReturnStr+Name+',' 
from dsr where ID=@iID
return stuff(@vReturnStr,len(@vReturnStr),1,'')
end
goselect A.ID 案件ID
,A.Info 案由
,dbo.MoreName(ID) '当事人姓名(name)'
from ajzt A
where exists(select top 1 0 from dsr B where A.ID=B.ID )
--显示结果
案件ID 案由 当事人姓名(name)
1 打架 张三,李四,王五
2 打架 赵六,邓七--删除测试环境
drop table ajzt
drop table dsr