以行为列的选择语句
以行为列的选择语句
提问于 2013-09-16 11:34:41
我有一个表USERS，它有两个列(RID，USERNAME)，我只是传递RID来获取USERNAME。
我将传递4个参数，如User1、User2、User3、User4，这些参数现在都有RID，我想要的结果如下：
Level1User Level2User Level3User Level4User
ABC DEF GHI JKL我尝试了以下查询：
SELECT
L1.USERNAME AS Level1User,
L2.USERNAME AS Level2User,
L3.USERNAME AS Level3User,
L4.USERNAME AS Level4User
FROM [USERS] AS L1,
[USERS] AS L2,
[USERS] AS L3,
[USERS] AS L4
WHERE L1.RID = @User1RID AND L2.RID = @User2RID AND L3.RID = @User3RID
AND L4.RID = @User4RID它运行良好，但当我为任何用户的RID使用RID时，它就失败了。我不知道该怎么做。
回答 3
Stack Overflow用户
回答已采纳
发布于 2013-09-16 11:39:21
使用Scalar重写它，您不必再关心NULL了：
SELECT
(SELECT USERNAME FROM [USERS] WHERE RID = @User1RID) AS Level1User,
(SELECT USERNAME FROM [USERS] WHERE RID = @User2RID) AS Level2User,
(SELECT USERNAME FROM [USERS] WHERE RID = @User3RID) AS Level3User,
(SELECT USERNAME FROM [USERS] WHERE RID = @User4RID) AS Level4UserStack Overflow用户
发布于 2013-09-16 11:41:34
您可以通过聚合来完成这一任务：
select max(case when u.RID = @User1RID then USERNAME end) as Level1User,
max(case when u.RID = @User2RID then USERNAME end) as Level2User,
max(case when u.RID = @User3RID then USERNAME end) as Level3User,
max(case when u.RID = @User4RID then USERNAME end) as Level4User
from [users] u
where u.RID in (@User1RID, @User2RID, @User3RID, @User4RID)Stack Overflow用户
发布于 2013-09-16 12:36:11
检查https://stackoverflow.com/a/13474228/2478298以获得答案
在你的情况下这会使
SELECT
L1.USERNAME AS Level1User,
L2.USERNAME AS Level2User,
L3.USERNAME AS Level3User,
L4.USERNAME AS Level4User
FROM [USERS] AS L1,
[USERS] AS L2,
[USERS] AS L3,
[USERS] AS L4
WHERE (L1.RID = @User1RID OR @User1RID IS NULL)
AND (L2.RID = @User2RID OR @User2RID IS NULL)
AND (L3.RID = @User3RID OR @User3RID IS NULL)
AND (L4.RID = @User4RID OR @User4RID IS NULL)希望能帮上忙。
致以敬意，
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18826959
复制相关文章
相似问题
腾讯云开发者
