表 a
uid name
1 aa
2 bb
3 cc表 b
uid select
1 语文
1 数学
2 语文
2 数学
2 英语
3 数学表a 表b 连接 查询结果为
uid name select
1 aa 语文数学
2 bb 语文数学英语
3 cc 数学
想要上面的查询结果 请问如何实现,先谢谢了!
uid name
1 aa
2 bb
3 cc表 b
uid select
1 语文
1 数学
2 语文
2 数学
2 英语
3 数学表a 表b 连接 查询结果为
uid name select
1 aa 语文数学
2 bb 语文数学英语
3 cc 数学
想要上面的查询结果 请问如何实现,先谢谢了!
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S,'')+select from 表2 where Col1=@Col1
return @S
end
go
select a.uid,a.[name],b.select from 表1 a
join
(
Select distinct uid,select=dbo.F_Str(uid) from 表2
) b
on a.uid=b.uid
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S,'')+[select] from 表2 where Col1=@Col1
return @S
end
go
select a.uid,a.[name],b.[select] from 表1 a
join
(
Select distinct uid,[select]=dbo.F_Str(uid) from 表2
) b
on a.uid=b.uid
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S,'')+[select] from 表2 where Col1=@Col1
return @S
end
go
select a.uid,a.[name],b.[select] from 表1 a
join
(
Select distinct uid,[select]=dbo.F_Str(uid) from 表2
) b
on a.uid=b.uid强大
select a.uid as uid, a.name as name, b.[select] from a,b where a.uid=b.uid
IF Object_Id('[dbo].[uf_GetName]') is not null
DROP FUNCTION [dbo].[uf_GetName]
goCREATE FUNCTION [dbo].[uf_GetName](@Uid int)
RETURNS varchar(100) AS
BEGIN
Declare @Name varchar(20),@Content varchar(100) Declare MyTest cursor FOR
SELECT [select]
FROM table_b
WHERE uid = @Uid OPEN MyTest
FETCH NEXT FROM MyTest
INTO @Name While @@FETCH_STATUS = 0
BEGIN
SET @Content = rtrim(@Name))+ ',' + isnull(@Content,'')
FETCH NEXT FROM MyTest
INTO @Name
END
CLOSE MyTest
DEALLOCATE MyTest
IF len(@Content) > 1
SET @Content = Substring(@Content,0,len(@Content))
RETURN @Content
ENDSELECT DISTINCT uid,name,[dbo].[uf_GetName]( ClientId) AS [select] FROM table_a
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S,'')+select from 表2 where Col1=@Col1
return @S
end
go
select a.uid,a.[name],b.select from 表1 a
join
(
Select distinct uid,select=dbo.F_Str(uid) from 表2
) b
on a.uid=b.uid