代码之家 › 专栏 › 技术社区 › Pankaj Agarwal

如何从表中获取数据

sql-server-2005 sql-server

Pankaj Agarwal · 技术社区 · 15 年前

我必须从下面提到的表格结构中选择球员姓名、队长姓名。

表结构如下。

创建表temp(playerid int,playername varchar(100),captainid int)

PlayerId     PlayerName     CaptainId
--------------------------------------
1            Dhoni          NULL
2            Sachin         1
3            Rahul          1

我得从这张桌子上选择球员的名字和队长的名字。谁能

4 回复 | 直到 15 年前

Dr. Rajesh Rolen 15 年前

select playerName, (select t2.playername from #temp t2 where t2.playerid = t.captainid  ) from #temp t


select t1.playername , t2.playername captain from #temp t1 left join #temp t2 on t1.captainid = t2.playerid

Mutation Person 15 年前

select t.PlayerName, t2.PlayerName as CaptainName
from #temp t
left join #temp t2 on t.CaptainId = t2.PlayerId

select t.PlayerName, isnull(t2.PlayerName,'None') as CaptainName
from #temp t
left join #temp t2 on t.CaptainId = t2.PlayerId

Adriaan Stander 15 年前

SELECT p.PlayerName,
 c.PlayerName CaptainName
FROM #temp p LEFT JOIN
 #temp c ON p.CaptainId = c.PlayerId

Ahmad Farid 15 年前

SELECT PlayerName, CaptainName 
FROM PlayerTable INNER JOIN CaptainTable
ON PlayerTable.CaptainID=CaptainTable.CaptainID
WHERE CaptainName = 'your query'

推荐文章