sql - 试图构造一个SQL查询麻烦

Question

大家好，我在使用 SQL 查询创建所需的输出时遇到了问题。我对此很陌生，我正在尝试练习。 SQL 查询首先为手球比赛构建表。

如何使用 SQL 查询获得此输出:

Winner       | GoalsFor | GoalsAgainst | Loser       | GroupID | Venue
---------------------------------------------------------------------------
Austria      | 22       | 22           | South Korea | A       | South Korea
Cuba         | 22       | 22           | Norway      | B       | Norway
Cuba         | 22       | 22           | Norway      | D       | Norway
France       | 19       | 17           | Austria     | A       | Austria
France       | 19       | 17           | Cuba        | D       | Cuba
Iceland      | 23       | 19           | South Korea | A       | Iceland
Iceland      | 30       | 28           | Austria     | A       | Iceland
Iceland      | 37       | 18           | France      | A       | France
Norway       | 23       | 19           | France      | D       | Norway
Russia       | 23       | 19           | Norway      | D       | Russia
Russia       | 30       | 28           | Cuba        | D       | Russia
Russia       | 37       | 18           | France      | D       | France
South Korea  | 23       | 19           | France      | A       | South Korea

使用此代码:

 create table Teams (
    teamid int primary key,
    country varchar(30) unique not null,
    continent varchar(20) not null,
    strength int not null
)
go

create table Groups (
    groupid char(1) primary key,
    numteams int
)
go

create table GroupsTeams (
    groupid char(1) foreign key references Groups,
    teamid int foreign key references Teams,
    primary key (groupid, teamid)
)
go

create table Games (
   gameid int primary key identity,
   hometeam int not null foreign key references Teams,
   awayteam int not null foreign key references Teams,
   groupid char(1) not null foreign key references Groups,
   homescore int,
   awayscore int,
   unique (hometeam, awayteam, groupid)
)
go

insert into Teams values (1, 'Iceland', 'Europe', 1)
go
insert into Teams values (2, 'South Korea', 'Asia', 2)
go
insert into Teams values (3, 'Austria', 'Europe', 3)
go
insert into Teams values (4, 'France', 'Europe', 4)
go
insert into Teams values (5, 'U.S.', 'America', 5)
go
insert into Teams values (6, 'Norway', 'Europe', 6)
go
insert into Teams values (7, 'Cuba', 'America', 7)
go
insert into Teams values (8, 'Russia', 'Europe', 8)
go 
insert into Teams values (9, 'Egypt', 'Africa', 9)
go

insert into Groups values ('A', 4)
go
insert into Groups values ('B', 4)
go
insert into Groups values ('C', 2)
go
insert into Groups values ('D', 2)
go

insert into GroupsTeams values ('A', 1)
go
insert into GroupsTeams values ('A', 2)
go
insert into GroupsTeams values ('A', 3)
go
insert into GroupsTeams values ('A', 4)
go
insert into GroupsTeams values ('B', 5)
go
insert into GroupsTeams values ('B', 6)
go
insert into GroupsTeams values ('B', 7)
go
insert into GroupsTeams values ('B', 8)
go
insert into GroupsTeams values ('C', 1)
go
insert into GroupsTeams values ('C', 3)
go
insert into GroupsTeams values ('D', 2)
go
insert into GroupsTeams values ('D', 4)
go

insert into Games values (1, 2, 'A', 23, 19)
go
insert into Games values (3, 4, 'A', 17, 19)
go
insert into Games values (1, 3, 'A', 30, 28)
go
insert into Games values (2, 4, 'A', 23, 19)
go
insert into Games values (4, 1, 'A', 18, 37)
go
insert into Games values (2, 3, 'A', 22, 22)
go
insert into Games values (8, 6, 'D', 23, 19)
go
insert into Games values (7, 4, 'D', 17, 19)
go
insert into Games values (8, 7, 'D', 30, 28)
go
insert into Games values (6, 4, 'D', 23, 19)
go
insert into Games values (4, 8, 'D', 18, 37)
go
insert into Games values (6, 7, 'D', 22, 22)
go
insert into Games values (6, 7, 'B', 22, 22)
go

Answer 1

你可以使用这样的东西，如果分数相同，我会包含一个值:

select 
  case when g.homescore > g.awayscore
      then ht.country
      when g.awayscore > g.homescore
      then at.country 
      else 'tie' end as winner,
  case when g.homescore > g.awayscore
      then g.homescore
      else  g.awayscore end as GoalsFor,
  case when g.homescore > g.awayscore
      then g.awayscore
      else  g.homescore end as GoalsAgainst,
  case when g.homescore > g.awayscore
      then at.country
      else  ht.country end as Loser,
  g.groupid,
  ht.country Venue
from games g
left join teams ht
  on g.hometeam = ht.teamid
left join teams at
  on g.awayteam = at.teamid
left join groups gp
  on g.groupid = gp.groupid
order by ht.country

参见 SQL Fiddle with Demo