时间戳

首页 > 解决方案 > PL/SQL 试图将 tableID 显示为另一个表名称

sql - PL/SQL 试图将 tableID 显示为另一个表名称

问题描述

我正在制作一个 NHL oracle sql 数据库。我正在尝试创建一个 SELECT 查询，它显示来自游戏表的 homeTeam，但显示来自团队表的 teamName。本质上，我希望输出为“SELECT hometeam, homecore, awayteam, awayscore”，但我不想显示 teamID，而是希望在 homeTeam 和 awayTeam 列中看到 teamNames。我相信这可以通过加入来完成，只是无法让它工作。

create table team (
  teamID        number(2,0) not null,
  teamAbb       varchar2(3),
  teamName      varchar2(20),
  location      varchar2(20),
  division      varchar2(20),
  conference    varchar2(20),
  constraint pk_team primary key (teamID)
);

create table game(
 gameID     number(2,0) not null,
 gameDate   date,
 homeTeam   number(2,0),
 homeScore  number(2,0),
 awayTeam   number(2,0),
 awayScore  number(2,0),
 constraint pk_game primary key (gameID),
 constraint fk_homeTeam foreign key (homeTeam) references team (teamID),
 constraint fk_awayTeam foreign key (awayTeam) references team (teamID)
);

插入这些

DECLARE

BEGIN

insert into team values(10, 'ANA', 'Ducks', 'Anaheim', 'Pacific', 'Western');
insert into team values(11, 'ARI', 'Coyotes', 'Arizona', 'Pacific', 'Western');
insert into team values(12, 'BOS', 'Bruins ', 'Boston', 'Atlantic', 'Eastern');
insert into team values(13, 'BUF', 'Sabers ', 'Buffalo', 'Atlantic', 'Eastern');

COMMIT;

END;

DECLARE

BEGIN

insert into game values (1, to_date('01-11-2020','mm-dd-yyyy'), 10, 2, 11, 1);
insert into game values (2, to_date('01-13-2020','mm-dd-yyyy'), 11, 5, 12, 6);
insert into game values (3, to_date('03-04-2020','mm-dd-yyyy'), 10, null, 13, null);

COMMIT;

END;

这就是我目前使用 SELECT 看到的内容在此处输入图像描述

标签： sqloracleplsql

解决方案

您将必须与团队表执行 2 次连接

询问

select t1.teamName AS HomeTeam, g.homeScore, t2.teamName AS AwayTeam, g.awayScore
from    game g
    inner join team t1 ON t1.teamID = g.homeTeam
    inner join team t2 ON t2.teamID = g.awayTeam

结果

HomeTeam    HomeScore    AwayTeam   AwayScore
---------------------------------------------
Ducks       2            Coyotes    1
Coyotes     5            Bruins     6
Ducks       NULL         Sabers     NULL

推荐阅读