一个好例子

假定现在有三张表
表名:User
  uid  name  email
  1  张三  12@das.com
  2  李四  1213@asd.com
  3  王五  asd@asd.com
表名：Part
  pid  name
  1  软件部
  2  财务部
  3  后勤部
关系表：User_Part
  pid  uid
    1    1
    1    2
    2    3
    3    3
-----------------------------------------
关系表中说明王五既在财务部又在后勤部

比如我现在要查出类似下面所有用户信息格式如下：
  name    email            part
  张三  12@das.com        软件部
  李四    1213@asd.com      软件部
  王五    asd@asd.com      财务部，后勤部 
----------------------------------------------------
怎么写SQL语句或者存储过能呢？

 

 

create table [User](uid int, name varchar(10), email varchar(20)) insert into [User] values(1 , '张三' , '12@das.com') insert into [User] values(2 , '李四' , '1213@asd.com') insert into [User] values(3 , '王五' , 'asd@asd.com') create table Part (pid int, name varchar(10)) insert into Part values(1 , '软件部') insert into Part values(2 , '财务部') insert into Part values(3 , '后勤部') create table User_Part(pid int, uid int) insert into User_Part values(1 , 1 ) insert into User_Part values(1 , 2 ) insert into User_Part values(2 , 3 ) insert into User_Part values(3 , 3 ) go --创建一个合并的函数 create function f_hb(@uid int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(name as varchar) from (select a.uid , b.name from user_part a,part b where a.pid = b.pid) t where uid = @uid set @str = right(@str , len(@str) - 1) return(@str) End go --调用自定义函数得到结果： select m.name,m.email,n.part from [user] m, (select distinct uid ,dbo.f_hb(uid) as part from user_part a,part b where a.pid = b.pid) n where m.uid = n.uid drop table [user] , part , user_part drop function dbo.f_hb /* name email part ---------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 张三 12@das.com 软件部 李四 1213@asd.com 软件部 王五 asd@asd.com 财务部,后勤部 （所影响的行数为 3 行）

 

 

--下面为sql server 2005利用OUTER APPLY的写法. create table [User](uid int, name varchar(10), email varchar(20)) insert into [User] values(1 , '张三' , '12@das.com') insert into [User] values(2 , '李四' , '1213@asd.com') insert into [User] values(3 , '王五' , 'asd@asd.com') create table Part (pid int, name varchar(10)) insert into Part values(1 , '软件部') insert into Part values(2 , '财务部') insert into Part values(3 , '后勤部') create table User_Part(pid int, uid int) insert into User_Part values(1 , 1 ) insert into User_Part values(1 , 2 ) insert into User_Part values(2 , 3 ) insert into User_Part values(3 , 3 ) go select t1.name,t1.email,t2.part from [user] t1, ( SELECT uid , name as part FROM(SELECT DISTINCT uid FROM (select a.uid , b.name from user_part a,part b where a.pid = b.pid) m)A OUTER APPLY( SELECT [name]= STUFF(REPLACE(REPLACE( ( SELECT name FROM (select a.uid , b.name from user_part a,part b where a.pid = b.pid) N WHERE uid = A.uid FOR XML AUTO ), '', ''), 1, 1, '') )N ) t2 where t1.uid = t2.uid drop table [user] , part , user_part /* name email part ---------- -------------------- --------------- 张三 12@das.com 软件部 李四 1213@asd.com 软件部 王五 asd@asd.com 财务部,后勤部 (3 行受影响) */