如何为SQL子查询按DISTINCT球员分组并关联多表别名统计？

Got it, let's adjust your original query to calculate stats per player instead of global totals. Here's a clean, efficient approach using window functions to track each player's recent matches:

WITH player_matches AS (
    -- Get all unique players and every match they participated in
    SELECT 
        p.player,
        r.f_datetime,
        r.f_total_ftg,
        -- Rank matches for each player by most recent first
        ROW_NUMBER() OVER (PARTITION BY p.player ORDER BY r.f_datetime DESC) AS match_rank
    FROM results r
    JOIN (
        -- Combine distinct players from both columns
        SELECT DISTINCT f_player1 AS player FROM results
        UNION
        SELECT DISTINCT f_player2 AS player FROM results
    ) p 
        ON r.f_player1 = p.player OR r.f_player2 = p.player
)
-- Aggregate stats for last 8 and last 16 matches per player
SELECT
    player,
    SUM(CASE WHEN match_rank <= 8 THEN 1 ELSE 0 END) AS count_last_8,
    SUM(CASE WHEN match_rank <= 8 THEN f_total_ftg ELSE 0 END) AS goals_8,
    SUM(CASE WHEN match_rank <= 16 THEN 1 ELSE 0 END) AS count_last_16,
    SUM(CASE WHEN match_rank <= 16 THEN f_total_ftg ELSE 0 END) AS goals_16
FROM player_matches
GROUP BY player
ORDER BY player;

How This Works: #

1. CTE player_matches:

   • First, we create a list of all unique players using UNION to combine distinct values from f_player1 and f_player2.
   • We then join this player list back to the results table to pull every match each player was part of.
   • The ROW_NUMBER() window function assigns a rank to each match for a player, starting at 1 for their most recent match (sorted by f_datetime DESC).

2. Main Aggregation Query:

   • We use CASE statements to count and sum values only for matches within the last 8 or 16 (based on the match_rank).
   • If a player has fewer than 8 or 16 matches, the sums will automatically reflect their actual number of games played—no extra handling needed.

This query will output exactly the player-level stats you're looking for, matching your example structure:

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