NBA篮球赛事查询：基于Teams与Games表的技术问询

Hey there, let's work through building that NBA game query feature using your Teams and Games tables. First, let's make sure we're on the same page with the table structures:

数据表结构说明 #

Teams表 #

This table maps team abbreviations to full names, with each TeamID combining city and nickname info:

Games表 #

This table stores sequences of home games per team. Each column represents a single game (Game_1, Game_2, etc.), and each cell uses the format HomeTeamID, AwayTeamID to denote the matchup:

核心优化与查询实现 #

Quick note: your current Games table structure (column-based games) isn't ideal for flexible queries. I'd recommend normalizing it to a row-based structure first—this will simplify every query you want to run. Here's how to do that, plus examples of common queries:

Step 1: Normalize the Games Table #

First, create a structured NormalizedGames table to store each game as a separate row:

CREATE TABLE NormalizedGames (
  GameID VARCHAR(10),
  HomeTeamID VARCHAR(2),
  AwayTeamID VARCHAR(2)
);

-- Insert existing game data (adjust as you add more rows/columns)
INSERT INTO NormalizedGames (GameID, HomeTeamID, AwayTeamID)
VALUES
('Game_1', 'IP', 'PT'),
('Game_2', 'IP', 'DN'),
('Game_3', 'IP', 'MG'),
('Game_4', 'IP', 'DP'),
('Game_5', 'IP', 'DM'),
('Game_1', 'DM', 'PT'),
('Game_2', 'DM', 'MG'),
('Game_3', 'DM', 'DP');

Step 2: Common Query Examples #

1. Get All Home Games for a Specific Team

Let's say you want all home games for the Indiana Pacers (IP), with full team names:

SELECT 
  ng.GameID,
  home.TeamName AS HomeTeam,
  away.TeamName AS AwayTeam
FROM 
  NormalizedGames ng
JOIN 
  Teams home ON ng.HomeTeamID = home.TeamID
JOIN 
  Teams away ON ng.AwayTeamID = away.TeamID
WHERE 
  home.TeamID = 'IP';

2. Get All Away Games for a Specific Team

To find every game where the Portland Trailblazers (PT) are the away team:

SELECT 
  ng.GameID,
  home.TeamName AS HomeTeam,
  away.TeamName AS AwayTeam
FROM 
  NormalizedGames ng
JOIN 
  Teams home ON ng.HomeTeamID = home.TeamID
JOIN 
  Teams away ON ng.AwayTeamID = away.TeamID
WHERE 
  away.TeamID = 'PT';

3. Get All Matchups Between Two Teams

If you want to see every game between the Dallas Mavericks (DM) and Indiana Pacers (IP):

SELECT 
  ng.GameID,
  home.TeamName AS HomeTeam,
  away.TeamName AS AwayTeam
FROM 
  NormalizedGames ng
JOIN 
  Teams home ON ng.HomeTeamID = home.TeamID
JOIN 
  Teams away ON ng.AwayTeamID = away.TeamID
WHERE 
  (ng.HomeTeamID = 'DM' AND ng.AwayTeamID = 'IP')
  OR (ng.HomeTeamID = 'IP' AND ng.AwayTeamID = 'DM');

内容的提问来源于stack exchange，提问作者Bryan