使用单个查询从两行获取数据 [英] Fetch data from two rows using a single query

问题描述

如何从同一个表的不同行获取用户名，给定，第一人的UID。 ClientUID是我们在这里链接两行的关系。



表格如下。

How can I fetch Username(s) from different rows of same table, given, UID of 1st person. ClientUID is the relation we have here to link both the rows.

The table is as follows.

---------------------------------------------------
UID............Username............ClientUID
1111...............Rick.....................2222
2222...............John....................3333

我想要使用单个SQL查询获取用户名Rick和John，我有UserId = 1111数据。目前我已将它们分解为两个查询，但寻找更好的解决方案。请建议是否。

I want to fetch Usernames Rick and John using single SQL query and I have UserId = 1111 data with me. Currently I have broke them down to two queries, but looking for better solution. Please suggest if any.

推荐答案

如果我理解你的问题，你想要同一行的两个用户。

如果这样是你可以使用join的情况。所以像

If I understand your question correctly, you want both of the users on the same row.
If this is the case you can use join. So something like

SELECT *
FROM user u1
     INNER JOIN user u2 on u1.clientuid = u2.uid
WHERE u1.uid = 1111;

有待考虑的事项如果ClientUID不是必需的，您可以使用LEFT OUTER JOIN而不是INNER JOIN。看看使用外部联接 [ ^ ]



编辑



示例如何为结果集中的列指定别名：

On thing to consider is that if the ClientUID is not mandatory, you could use LEFT OUTER JOIN instead of INNER JOIN. Have a look at Using Outer Joins[^]

EDIT

Example how to give an alias name for a column in the result set:

SELECT u1.UID         AS UID1,
       u1.UserName    AS User1,
       u2.UID         AS UID2,
       u2.UserName    AS User2
FROM user u1
     INNER JOIN user u2 on u1.clientuid = u2.uid
WHERE u1.uid = 1111;

通过上面的示例，您可以从中获取值读者带

• 读者[UID1]
• 读者[User1]
• 读者[UID2]
• 读者[User2]




我可能使用Common Table Expression获取第一行，然后使用UNION获取第二行。



例如





I'd likely use a Common Table Expression to fetch the first row, then a UNION to fetch second.

For Example



WITH cte AS
(
  SELECT * FROM table WHERE UID=@uid
)
SELECT * FROM cte
UNION ALL 
SELECT table.* FROM table INNER JOIN cte ON table.UID=cte.ClientUID





对于更复杂的东西，你可能需要使用递归CTE。



For something more complex, you might need to use a recursive CTE.




这篇关于使用单个查询从两行获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！