T-SQL比较2用户名

下面我有3个表。 我想比较每个用户的角色。 这就是我想表达的是约翰有2个角色,而简也1.目前我只显示它们之间的共同作用。

  • Sec_role (PK R_KEY, rname)
  • Role_User(PK R_KEY, PK USER_KEY)
  • User(PK USER_KEY, Name)

样本数据:

Sec_Role Role_User User R_Key|rname| R_Key|User_Key User_KEY|NAME 1 |Analyst 1 |1 1 |John 2 |Sysadmin 2 |1 2 |Jane 2 |2

我试图让

User | Role | User2 | Role2 John | Analyst | Jane | (Empty because she isn't an analyst) John | sysadmin| Jane |Sysadmin



SELECT U.Name AS User1, U2.Name AS User2, R.rname AS Role1 R2.Rname AS Role2 FROM Sec_Role AS R LEFT OUTER JOIN Role_User AS RU ON R.R_Key=RU.R_Key LEFT OUTER JOIN User AS U ON U.User_Key=RU.User_Key LEFT OUTER JOIN Sec_Role AS R2 ON R2.R_Key=R.R_Key <---(I think this is the issue here) LEFT OUTER JOIN Role_User AS RU2 ON R2.R_Key=RU2.R_Key LEFT OUTER JOIN User AS U2 ON U2.User_Key=RU2.User_Key WHERE U.Name ='John' AND U2.Name='Jane'

目前,我得到这两个用户的交集。

User | Role | User2 | Role2 John | sysadmin| Jane |Sysadmin

--------------解决方案-------------

WHERE U.Name ='John'
OR U2.Name='Jane'

注意一个或不是AND

为什么4列

SELECT U.Name AS User,
R.rname AS Role
FROM Sec_Role AS R
LEFT OUTER JOIN Role_User AS RU
ON R.R_Key=RU.R_Key
LEFT OUTER JOIN User AS U
ON U.User_Key=RU.User_Key
where uName in ('','')
order by 1, 2

尝试这个

SELECT U.Name AS User1,
U2.Name AS User2,
R.rname AS Role1
R2.Rname AS Role2
FROM Sec_Role AS R
LEFT OUTER JOIN Role_User AS RU
ON R.R_Key=RU.R_Key
LEFT OUTER JOIN User AS U
ON U.User_Key=RU.User_Key
and U.Name ='John'

LEFT OUTER JOIN Sec_Role AS R2
ON R2.R_Key=R.R_Key <---(I think this is the issue here)
LEFT OUTER JOIN Role_User AS RU2
ON R2.R_Key=RU2.R_Key
LEFT OUTER JOIN User AS U2
ON U2.User_Key=RU2.User_Key
AND U2.Name='Jane'

分类:TSQL 时间:2015-03-15 人气:0
本文关键词: TSQL
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.273 (s). 10 q(s)