当前位置:Gxlcms > mysql > 来自中油瑞飞的SQL笔试题20131202

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

时间:2021-07-01 10:21:17 帮助过:34人阅读

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;
输出结果:

\

人气教程排行