来自中油瑞飞的SQL笔试题20131202

1、有三张表，用户表，用户角色表，角色表，使用sql显示如下内容： 用户ID，用户名，超级管理员，录入员，会计 也就是角色用逗号分隔。 解： 1、填充数据到表User select * from [User] INSERT INTO [northwind].[dbo].[User] ([ID] ,[NAME]) VALUES (1 ,zha

1、有三张表，用户表，用户角色表，角色表，使用sql显示如下内容：
用户ID，用户名，超级管理员，录入员，会计
也就是角色用逗号分隔。
解：

1、填充数据到表User
select * from [User]
INSERT INTO [northwind].[dbo].[User]
([ID]
,[NAME])
VALUES
(1
,'zhaohy')
INSERT INTO [northwind].[dbo].[User]
([ID]
,[NAME])
VALUES
(2
,'zhangyy')
GO

2、填充数据到表role
select * from [role]
INSERT INTO [northwind].[dbo].[Role]
([ID]
,[RoleName])
VALUES
(1
,'senior software engineer')
INSERT INTO [northwind].[dbo].[Role]
([ID]
,[RoleName])
VALUES
(2
,'project manager')
INSERT INTO [northwind].[dbo].[Role]
([ID]
,[RoleName])
VALUES
(3
,'UI Disigner')
INSERT INTO [northwind].[dbo].[Role]
([ID]
,[RoleName])
VALUES
(4
,'Tester')
GO
3、填充数据到表Role_User
select * from Role_User
INSERT INTO [northwind].[dbo].[Role_User]
([RoleID]
,[UserID])
VALUES
(1
,1)
GO
INSERT INTO [northwind].[dbo].[Role_User]
([RoleID]
,[UserID])
VALUES
(2
,1)
INSERT INTO [northwind].[dbo].[Role_User]
([RoleID]
,[UserID])
VALUES
(3
,1)
INSERT INTO [northwind].[dbo].[Role_User]
([RoleID]
,[UserID])
VALUES
(4
,2)

4、查询出来：
drop table #result;
select * into #result from (select u.ID,u.NAME,ru.RoleID,r.RoleName from [user] u inner join Role_User ru on ru.UserID =u.ID inner join [Role] r
on ru.RoleID=r.ID) as t;
select * from #result;
select id,name, [RoleName] = stuff((select ',' + [RoleName] from #result t where id = #result.id for xml path('')) , 1 , 1 , '')
from #result
group by id ,name;
drop table #result;
输出结果：