如何使用SQL查询生成指定的团队两两对阵输出结果？

Got it, let's tackle this problem step by step. First, let's set up our test table with the team data you provided. We'll create a teams table and insert the records:

CREATE TABLE teams (
    id INT,
    team_name VARCHAR(50)
);

INSERT INTO teams VALUES
(1, 'India'),
(2, 'Pakistan'),
(3, 'Srilanka'),
(4, 'Australia');

Next, we can use a self-join combined with window functions to generate the unique pairwise matchups in the exact format you need:

SELECT
    ROW_NUMBER() OVER (ORDER BY t2.id DESC, t1.id DESC) AS match_number,
    CONCAT(t2.team_name, ' VS ', t1.team_name) AS match_up
FROM
    teams t1
JOIN
    teams t2 ON t1.id < t2.id
ORDER BY
    match_number;

Let's break down what this query does: #

• Self-Join: By joining the teams table to itself (aliased as t1 and t2), we can create all possible combinations of teams.
• Filter Duplicate Matchups: The condition t1.id < t2.id ensures we only get unique pairs (we don't want both "India VS Pakistan" and "Pakistan VS India" in the results).
• Format Matchup Text: The CONCAT function combines the team names into the "TeamA VS TeamB" format, aligning with your output where the higher-ID team comes first.
• Generate Match Numbers: The ROW_NUMBER() window function assigns a sequential number to each matchup, ordered by t2.id descending and t1.id descending to match your desired output order.
• Sort Results: Finally, we order by match_number to ensure the output is in the exact sequence you provided.

Running this query will produce: #

match_number | match_up
-------------|-------------------
1            | Pakistan VS India
2            | Srilanka VS Pakistan
3            | Srilanka VS India
4            | Australia VS Srilanka
5            | Australia VS Pakistan
6            | Australia VS India

内容的提问来源于stack exchange，提问作者Md Saud Alam