TblType
TypeId TypeName
1 动作
2 喜剧
3 爱情
4 历史
5 战争
6 剧情
TblMovie
MovieId MovieName
1 画皮
TblMovieTypeList
ListId MovieId TypeId
1 1 1
2 1 3
3 1 6
我要得到查询结果是
MovieId MovieName MovieType
1 画皮 动作,爱情,剧情
TypeId TypeName
1 动作
2 喜剧
3 爱情
4 历史
5 战争
6 剧情
TblMovie
MovieId MovieName
1 画皮
TblMovieTypeList
ListId MovieId TypeId
1 1 1
2 1 3
3 1 6
我要得到查询结果是
MovieId MovieName MovieType
1 画皮 动作,爱情,剧情
create function getname(@movieid int)
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+',','')+ typename from TblType a,TblMovieTypeList b where a.typeid=b.typeid and b.movieid=@movieid
return @sql
end
goselect *,MovieType=dbo.getname(movieid) from TblMovie
insert tbltype
select 1 , '动作'union all
select 2 , '喜剧' union all
select 3 , '爱情' union all
select 4 , '历史' union all
select 5 , '战争' union all
select 6 , '剧情' create table tblmovie(moveid int,moviename varchar(100))
insert tblmovie
select 1,'画皮'create table tblmovietypelist(listid int,movieid int,typeid int)
insert tblmovietypelist
select 1 , 1 , 1 union all
select 2 , 1 , 3 union all
select 3 , 1 , 6 select distinct a.movieid ,b.typename,e.moviename into #aaa from tblmovietypelist a join tbltype b on a.typeid =b.typeid join (
select c.movieid as movieid ,d.moviename as moviename from tblmovietypelist c join tblmovie d on c.movieid =d.moveid ) e
on a.movieid =e.movieid
----drop table #aaa
declare @typename varchar(100),
@moviename varchar(100),
@movieid int
update #aaa
set @typename=case when @movieid=movieid and @moviename=moviename then @typename+','+typename else typename end,
@movieid=movieid ,
@moviename=moviename ,
typename=@typename
-----select * from #aaa
select movieid,MAX(typename) as typename ,moviename from #aaa group by movieid,moviename
declare @TblType table (TypeId int,TypeName varchar(4))
insert into @TblType
select 1,'动作' union all
select 2,'喜剧' union all
select 3,'爱情' union all
select 4,'历史' union all
select 5,'战争' union all
select 6,'剧情'--select * from @TblTypedeclare @TblMovie table (MovieId int,MovieName varchar(4))
insert into @TblMovie
select 1,'画皮'--select * from @TblMoviedeclare @TblMovieTypeList table (ListId int,MovieId int,TypeId int)
insert into @TblMovieTypeList
select 1,1,1 union all
select 2,1,3 union all
select 3,1,6select aa.*,bb.TypeName into #T from
(
select a.*,b.MovieId from @TblType a left join @TblMovieTypeList b on a.TypeId=b.ListId
)
bb
right join @TblMovie aa on aa.MovieId=bb.MovieIdselect MovieId,MovieName, TypeName=stuff((select ','+TypeName from #T t where MovieId=#T.MovieId for xml path('')), 1, 1, '')
from #T
group by MovieId,MovieName drop table #T
/*
MovieId MovieName TypeName
----------- --------- -----------------
1 画皮 动作,喜剧,爱情
*/
create table TblMovieType(MovieTypeId int,MovieType varchar(20))
insert into TblMovieType select 1,'动作'
insert into TblMovieType select 2,'悬疑'
insert into TblMovieType select 3,'惊悚'
insert into TblMovieType select 4,'历史'
insert into TblMovieType select 5,'战争' create table TblMovie(MovieId int,MovieName varchar(20),MovieTypeId varchar(40))
insert into TblMovie select 1,'夺命手机','1,2,3'
gocreate function f_str(@MovieTypeId varchar(40))
returns varchar(200)
as
begin
declare @MovieType varchar(200)
set @MovieType=','+@MovieTypeId+','
select @MovieType=replace(@MovieType,rtrim(MovieTypeId),MovieType) from TblMovieType
set @MovieType=substring(@MovieType,2,len(@MovieType)-2)
return @MovieType
end
goselect MovieId,MovieName,dbo.f_str(MovieTypeId) as MovieType from TblMovie
/*MovieId MovieName MovieType
----------- -------------------- -------------------
1 夺命手机 动作,悬疑,惊悚
*/
godrop function f_str
drop table TblMovieType,TblMovie
go