SQL 分组后 字段 拼接效果(自定义函数) 面试题

create function GetUserNames(@userDptID int)
returns nvarchar(500)
as

begin
declare @UserID int
declare @UserNames nvarchar(300)

select @UserID = min( UserID ) from UserInfo where UserDpt=@userDptID
set @UserNames=''
while @UserID is not null
begin
    select @UserNames=@UserNames+','+UserName from UserInfo where UserID = @UserID and UserDpt=@userDptID
    select @UserID = min(UserID) from UserInfo where UserID > @UserID and UserDpt=@userDptID
end
set @UserNames=substring(@UserNames,2,len(@UserNames))

return @UserNames
end

 

--=========================

select UserDpt,max(dbo.GetUserNames(UserDpt)) as UserNames from UserInfo
group by UserDpt

 

 

--=========================

SQL 代码:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserInfo](
 [UserID] [int] IDENTITY(1,1) NOT NULL,
 [UserName] [varchar](50) NULL,
 [UserDpt] [int] NULL,
 [UserFlag] [varchar](50) NULL,
 [UserBorn] [datetime] NULL,
 [UserBornStr] [int] NULL,
 CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED
(
 [UserID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

 

 

select UserDpt,max(dbo.getFull(UserDpt)) from UserInfo
group by UserDpt

create function getFull(@deptID int)
returns nvarchar(50)
as
begin
 declare @sql varchar(1000)
 select @sql=isnull(@sql,'') +','+UserName from UserInfo where UserDpt=@deptID
 return @sql
end