sql-server - SQL查询问题

Question

我是 StackOverflow 的新手，也是 SQL Server 的新手，我希望你能帮我解决一些麻烦的查询。

这是我的数据库结构(一半是西类牙文，希望没关系)

Database

我的问题是，我现在不知道如何进行查询，说明哪支球队是本地球队，哪支球队是访客(使用表 TMatch，知道体育场只属于一支球队)

这是我所能得到的

Select P.NroMatch, (select * from fnTeam (P.TeamA)) as TeamA,(select * from fnTeam (P.TeamB)) as TeamB,
    (select * from fnEstadium (P.CodEstadium)) as Estadium, (cast(P.GolesTeamA as varchar)) + '-' + (cast(P.GolesTeamA as varchar)) as Score,
    P.Fecha
from TMatch P

使用这个函数:

If object_id ('fnTeam','fn')is not null
drop function fnTeam
go
create function fnTeam(@CodTeam varchar(5))
returns table 

return(Select Name from TTeam where CodTeam = @CodTeam)
go

select * from fnTeam ('Eq001')
go
----****
If object_id ('fnEstadium','fn')is not null
drop function fnEstadium
go
create function fnEstadium(@CodEstadium varchar(5))
returns table 

return(Select Name from TEstadium where CodEstadium = @CodEstadium)
go

我希望我已经解释清楚了，我提前感谢你的帮助

编辑:

谢谢你的帮助，这就是我一直在寻找的

Select P.NroMatch, 
CASE 
    WHEN Ts.CodTeam= Ta.CodTeamTHEN Ta.Name
    ELSE Tb.Name
END 
As TeamLocal, 
CASE 
    WHEN Ts.CodTeam<> Ta.CodTeamTHEN Ta.Name
    ELSE Tb.Name
END 
As TeamVisitante,
Ts.Name as Estadium, 
(cast(P.GolesTeamA as varchar)) + '-' + (cast(P.GolesTeamB as varchar)) as Score,     
P.Fecha
from 
   TMatch P 
   join TTeamTa ON Ta.CodTeam= P.TeamA
   join TTeamTb ON Tb.CodTeam= P.TeamB
   join TEstadium Ts ON Ts.CodEstadium = P.CodEstadium  

Answer 1

你不需要为此使用“查找”函数(也不应该)，连接是更好的方法:

Select 
P.NroMatch, 
Ta.Name as TeamA,
Tb.Name as TeamB,     
Ts.Name as Estadium, 
cast(P.GolesTeamA as varchar)) + '-' + (cast(P.GolesTeamA as varchar) as Score,     
P.Fecha,
CASE 
    WHEN Ts.CodTeam = Ta.Name THEN Ta.Name
    ELSE Tb.Name
END As HomeTeam,
CASE 
    WHEN Ts.CodTeam <> Ta.Name THEN Ta.Name 
    ELSE Tb.Name 
 END As VistorTeam
from 
   TMatch P 
   join TTeam Ta ON Ta.CodTeam = P.TeamA
   join TTeam Tb ON Tb.CodTeam = P.TeamB
   join TEstadium Ts ON Ts.CodEstadium = P.CodEstadium

如果您是 SQL 新手，搜索 SO 中的一些资源可能会很有用，例如:

SQL Tutorial

SQL Tutorial