我想做一个奇怪的搜索，不知道SQL可以做么？

Question

create table table1(RoleId int, RoleName varchar(10))
insert table1
select 1, 'manager'
union all select 2, 'sales'create table table2(RoleId int, UserName varchar(10))
insert table2
select 1, 'wang'
union all select 1, 'zhao'
union all select 2, 'li'
union all select 2, 'qian'select t1.RoleId, t1.RoleName, t2.UserName, identity(int, 1, 1) as tid
INTO #tmp
from table1 as t1 JOIN table2 as t2
ON t1.RoleId = t2.RoleIdselect RoleId, RoleName+',', 
 Max(Case When rn = 1 THEN UserName else '' end)
+Max(Case When rn = 2 Then ';'+UserName Else '' END)
+Max(case When rn = 3 Then ';'+UserName Else '' End)
+Max(Case when rn = 4 then ';'+UserName Else '' End) as UserName
from 
( Select RoleId, RoleName, UserName, 
(Select count(*) from #tmp as B
 Where B.RoleId = A.RoleId
 And B.tid <= A.tid) as rn
from #tmp as A) as D
group by RoleId, RoleNamedrop table #tmpRoleId                  UserName
----------- ----------- -------------------------------------------
1           manager,    wang;zhao
2           sales,      li;qian

Answer 1

建立functioncreate function fn_test(@RoleId int)
returns varchar(100)
AS
begin
declare @UserName varchar(100)
set @UserName=''
select @UserName =@UserName+';'+UserName
from table2 
where RoleID=@RoleID
if len(@UserName)>0
 set @UserName=stuff(@UserName,1,1,'')return @UserName
endGOselect RoleId, RoleName ,dbo.fn_test(RoleID) as UserName
from Table1

Answer 2

create function GetValue(@id int)
returns varchar(100)
as 
begin
    declare @sql varchar(1000)
    select @sql=isnull(@sql+';','')+UserName from [Table2] where id=@id
    return @sql
end
go
select a.*,b.UserName from 
[Table1] aa 
inner join 
(select RoleId,dbo.GetValue(RoleId) UserName from [Table2] group by RoleId)bb
on aa.RoleId=bb.RoleId

Answer 3

CREATE TABLE Table1
(
RoleId INT,
RoleName VARCHAR(20)
)
INSERT INTO Table1 
SELECT 1,'manager' UNION ALL
SELECT 2,'sales' 
CREATE TABLE Table2
(
RoleId INT,
UserName VARCHAR(20)
) 
INSERT INTO Table2 
SELECT 1,'wang' UNION ALL
SELECT 1,'zhao' UNION ALL
SELECT 2,'li'  UNION ALL
SELECT 2,'qian' GO
CREATE FUNCTION F_UNION (@ID INT,@TYPE INT)
RETURNS VARCHAR(200)
AS
BEGIN
  DECLARE @re VARCHAR(200)
  SET @re = ''  IF @TYPE = 0
   SELECT @re = @re + ','+ ISNULL(RoleName,'') FROM Table1 WHERE @ID = RoleId
  ELSE
   SELECT @re = @re + ';'+ ISNULL(UserName,'') FROM Table2 WHERE @ID = RoleId  RETURN (STUFF(@re,1,1,''))
END
GOSELECT RoleId,RoleName=DBO.F_UNION(RoleId,0), UserName=DBO.F_UNION(RoleId,1)
 FROM Table1 GROUP BY RoleId
DROP FUNCTION F_UNION
DROP TABLE TABLE1,TABLE2RoleId      RoleName                                                                                                                                                                                                 UserName                                                                                                                                                                                                 
RoleId      RoleName                                           UserName                                           
----------- -------------------------------------------------- -------------------------------------------------- 
1           manager                                            wang;zhao
2           sales                                              li;qian（所影响的行数为 2 行）

Answer 4

我想知道如果不用函数，直接用Select 语句可以做到么？谢谢！

Answer 5

请问这个搜索为什么要使用函数？不可以把他们包含在SELECT语句里面么？因为我的系统不支持自定义函数！可以使用SQL2005的函数。

Answer 6

因为比如说同个ID有十行不同的数据,如果你想一条语句的话,那么就只有把这些集合按照不同联合起来的话,那么就要写起码9个JOIN,然后把1.该列+2.该列+..+9.该列.这样的效率是非常低的,而且语句昂长.而且如果是100行呢?所以如果是一两行倒是不存在,如果行数非常多,那么就只有使用函数了!:) GOOD LUCK!