求一个sql语句

Question

本帖最后由 ysj747 于 2010-08-17 15:38:05 编辑

Answer 1

select a.id,b.username,case when c.id=1 then c.rolename else '' end as 管理员
,case when c.id=2 then c.rolename else '' end as 员工
,case when c.id=3 then c.rolename else '' end as 读者
from userrole a
left join [user] b on a.userid=b.id
left join role c on a.roleid=c.id

Answer 2

create proc getUser
@userid varchar(10),
@psw varchar(20)
as
select a.id,b.username,case when c.id=1 then c.rolename else '' end as 管理员
,case when c.id=2 then c.rolename else '' end as 员工
,case when c.id=3 then c.rolename else '' end as 读者
from userrole a
left join [user] b on a.userid=b.id
left join role c on a.roleid=c.id
where b.id=@userid and b.password=@psw
go

Answer 3

---测试数据---
if object_id('[user]') is not null drop table [user]
go
create table [user]([ID] int,[Username] varchar(3),[password] varchar(3))
insert [user]
select 1,'aaa','sss'
if object_id('[role]') is not null drop table [role]
go
create table [role]([ID] int,[Rolename] varchar(6))
insert [role]
select 1,'管理员' union all
select 2,'员工' union all
select 3,'读者'
if object_id('[userrole]') is not null drop table [userrole]
go
create table [userrole]([ID] int,[UserID] int,[roleID] int)
insert [userrole]
select 1,1,1 union all
select 2,1,2 union all
select 3,1,3
 
---查询---
select 
  a.ID,
  a.username,
  [role]=stuff((select ','+Rolename from [role] x,[userrole] y where x.ID=y.roleID and y.userid=a.id for xml path('')),1,1,'')
from 
  [user] a ,[userrole] c
where 
  a.ID=c.userid 
group by 
  a.ID,
  a.username---结果---
ID          username role
----------- -------- --------------------------
1           aaa      管理员,员工,读者(1 行受影响)

Answer 4

--> 测试数据：#user
if object_id('tempdb.dbo.#user') is not null drop table #user
create table #user(ID int, Username varchar(8), password varchar(8))
insert into #user
select 1, 'aaa', 'sss'
--> 测试数据：#role
if object_id('tempdb.dbo.#role') is not null drop table #role
create table #role(ID int, Rolename varchar(8))
insert into #role
select 1, '管理员' union all
select 2, '员工' union all
select 3, '读者'
--> 测试数据：#userrole
if object_id('tempdb.dbo.#userrole') is not null drop table #userrole
create table #userrole(ID int, UserID int, roleID int)
insert into #userrole
select 1, 1, 1 union all
select 2, 1, 2 union all
select 3, 1, 3;with cte as
(select c.UserID, d.Rolename from #userrole c join #role d on c.roleID = d.ID)
select
ID, Username, [role] = stuff((select ','+Rolename from cte where UserID=t.ID for xml path('')),1,1,'')
from
#user as t group by ID, Username/*
ID          Username role
----------- -------- ----------------
1           aaa      管理员,员工,读者
*/