高级 SQL 面试题

1. 编写 SQL 查询语句，生成亚洲杯板球比赛赛程。

来源：

源表Teams由一列Country组成，其中包含参加亚洲杯的国家/地区列表。

CREATE TABLE TEAMS(
   COUNTRY VARCHAR(50)
);

INSERT INTO TEAMS VALUES('India');
INSERT INTO TEAMS VALUES('Srilanka');
INSERT INTO TEAMS VALUES('Bangladesh');
INSERT INTO TEAMS VALUES('Pakistan');

预期输出：

输出结果应提供所有参加亚洲杯的球队之间可能的比赛对阵表。

解决方案：

为了生成各队之间的赛程安排，我们需要对源表Teams进行自连接。我们需要添加一些额外的字段来帮助进行自连接，如下所示。

• 一个虚拟字段，用作表自连接的公共字段。（可选）
• 为每个国家分配一个唯一的ID，这有助于删除重复的设备。

SELECT 
   1 AS DUMMY,
   CASE 
      WHEN COUNTRY = 'India' THEN 1
      WHEN COUNTRY = 'Srilanka' THEN 2
      WHEN COUNTRY = 'Bangladesh' THEN 3
      WHEN COUNTRY = 'Pakistan' THEN 4
   ELSE 0 END AS ID,
   COUNTRY
 FROM TEAMS

上述查询的输出结果如下：

根据Dummy和ID字段对 TEAMS 表执行自连接，如下面的查询所示，以获得预期的输出。

WITH TEAM AS(
   SELECT 1 AS DUMMY,
   CASE 
      WHEN COUNTRY = 'India' THEN 1
      WHEN COUNTRY = 'Srilanka' THEN 2
      WHEN COUNTRY = 'Bangladesh' THEN 3
      WHEN COUNTRY = 'Pakistan' THEN 4
   ELSE 0 END AS ID,
   COUNTRY
   FROM TEAMS
)
SELECT 
   t1.COUNTRY "TEAM-A", 
   t2.COUNTRY "TEAM-B" 
FROM TEAM t1 JOIN TEAM t2 
ON t1.DUMMY = t2.DUMMY
AND t1.ID < t2.ID;

如果我们不添加基于 ID 的连接条件，输出结果将如下所示，导致赛程重复，甚至出现球队与自身比赛的情况。虚拟字段有助于生成笛卡尔坐标输出，而ID字段则有助于过滤重复项。

2. 编写 SQL 查询，找出亚洲杯中每支球队的比赛场次、胜场数、负场数和平局数。

来源：

源表Match_Results包含亚洲杯比赛的结果。

CREATE TABLE MATCH_RESULTS(
TEAM_A VARCHAR(10),
TEAM_B VARCHAR(10),
RESULT VARCHAR(10)
);INSERT INTO MATCH_RESULTS VALUES('India','Bangladesh','India');
INSERT INTO MATCH_RESULTS VALUES('India','Pakistan','India');
INSERT INTO MATCH_RESULTS VALUES('India','Srilanka','');
INSERT INTO MATCH_RESULTS VALUES('Srilanka','Bangladesh','Srilanka');
INSERT INTO MATCH_RESULTS VALUES('Srilanka','Pakistan','Pakistan');
INSERT INTO MATCH_RESULTS VALUES('Bangladesh','Pakistan','Bangladesh');

预期输出：

输出结果应包含每支球队的比赛场次、胜场数、负场数和平局数，如下所示。

解决方案：

首先，从源表中列出比赛参赛队伍的详细信息以及比赛结果。为此，我们需要分别选择 Team_A 及其比赛结果，以及 Team_B 及其比赛结果，然后使用 UNION ALL 连接运算符将它们连接起来。查询结果如下所示。

要统计某支球队的比赛场次，请使用GROUP BY函数，根据“球队”字段，从上述输出结果中统计每支球队的场次。对于特定球队，其自身在结果字段中的出现次数之和即为该球队的胜场数；其他球队在结果字段中的出现次数之和即为该球队的负场数。如果结果字段为空，则这些出现次数之和即为该球队的平局数。

最终输出查询语句如下：

WITH MATCHES AS
(
SELECT TEAM_A AS TEAM, RESULT FROM Match_Results
UNION ALL
SELECT TEAM_B AS TEAM, RESULT FROM Match_Results
)
SELECT
TEAM,
COUNT(TEAM)  MATCHES_PLAYED,
SUM(CASE WHEN RESULT = TEAM THEN 1 ELSE 0 END)  WINS,
SUM(CASE WHEN RESULT IS NULL THEN 1 ELSE 0 END)  TIES,
SUM(CASE WHEN RESULT != TEAM THEN 1 ELSE 0 END)  LOSS
FROM MATCHES
GROUP BY TEAM;