Tournament breakdown (subquery order)

Question

In the database of hockey results, I have a table that stores statistics for each team in each game. For each match, there are (usually) two entries - one for each team. Records store team_id, match_id and team performance in this match - points earned, goals scored and many other statistics that are not relevant here.

Here is an approximate query to build a table of tournaments:

SELECT  team_id, 
        COUNT(match_id) AS total_matches,
        SUM(goals_for) AS total_goals_for, 
        SUM(points) AS total_points
FROM    match_teams
WHERE   match_id IN ([match IDs here])
GROUP BY team_id
ORDER BY total_points DESC;

I select all the entries for matches that are part of the tournament, and the points for each team, then sorted by points.

The problem is that the rules for breaking the connection are rather complicated. Let's look at the rule "if several teams are tied to order for points earned in games between this team."

, total_points team_id IN (16,25,36), :

SELECT  teamA.team_id,
        SUM(teamA.points) AS total_points_inbetween
FROM    match_teams as teamA 
    JOIN match_teams as teamB
    ON teamA.match_id = teamB.match_id 
        AND teamA.team_id <> teamB.team_id
WHERE   teamA.match_id IN ([match IDs here])
    AND teamA.id IN (16,25,36)
    AND teamB.id IN (16,25,36)
GROUP BY teamA.team_id
ORDER BY total_points_inbetween DESC;

- ORDER BY ? , ORDER BY total_points DeSC, [complicated_rule_1], total_goals_for DESC, [complicated_rule_2]

:

Match 10: team100 vs team200  2-1
Match 12: team100 vs team300  3-0
Match 15: team100 vs team400  1-2
Match 61: team100 vs team500  2-0
Match 62: team200 vs team300  5-1
Match 63: team200 vs team400  2-1
Match 66: team200 vs team500  0-3
Match 70: team300 vs team400  4-0
Match 73: team300 vs team500  5-1
Match 77: team400 vs team500  2-1

match_teams :

match_id    team_id     goals_for   points
10          100         2           3
10          200         1           0
12          100         3           3
12          300         0           0
15          100         1           0
15          400         2           3
61          100         2           3
61          500         0           0
62          200         5           3
62          300         1           0
63          200         2           3
63          400         1           0
66          200         0           0
66          500         3           3
70          300         4           3
70          400         0           0
73          300         5           3
73          500         1           0
77          400         2           3
77          500         1           0

( ) , :

team_id     total_points
100         9
200         6
300         6
400         6
500         3

3 , . :

Match 62: team200 vs team300  5-1
Match 63: team200 vs team400  2-1
Match 70: team300 vs team400  4-0

, :

match_id    team_id     goals_for   points
62          200         5           3
62          300         1           0
63          200         2           3
63          400         1           0
70          300         4           3
70          400         0           0

200 (6 ), team300 (3 ), team400 . team200 > team300 > team400.

Answer 1

:

DROP TEMPORARY TABLE IF EXISTS tmp_match_teams;
CREATE TEMPORARY TABLE tmp_match_teams
    SELECT m1.*, m2.team_id AS opponent_id
    FROM match_teams m1
    JOIN match_teams m2 ON m2.match_id = m1.match_id AND m2.team_id <> m1.team_id
    WHERE m1.match_id IN (10,12,15,61,62,63,66,70,73,77);

DROP TEMPORARY TABLE IF EXISTS tmp_result1;
CREATE TEMPORARY TABLE tmp_result1
    SELECT  team_id, 
            COUNT(match_id) AS total_matches,
            SUM(goals_for) AS total_goals_for, 
            SUM(points) AS total_points
    FROM    tmp_match_teams
    GROUP BY team_id;

DROP TEMPORARY TABLE IF EXISTS tmp_result2;
CREATE TEMPORARY TABLE tmp_result2 SELECT team_id, total_points FROM tmp_result1;

SELECT r1.*, SUM(m.points) AS total_points_tie
FROM tmp_result1 r1
LEFT JOIN tmp_result2 r2 
    ON  r2.total_points = r1.total_points
    AND r2.team_id <> r1.team_id
LEFT JOIN tmp_match_teams m
   ON  m.team_id = r1.team_id
   AND m.opponent_id = r2.team_id
GROUP BY r1.team_id
ORDER BY r1.total_points DESC, total_points_tie DESC, r1.total_goals_for DESC;

: http://rextester.com/HXPXRX93310

tmp_match_teams - match_teams, match_ids, . oppenent_id, "" .

tmp_result1 .

tmp_result2 team_id, total_points tmp_result1. "" total_score, ( MySQL).

"" total_score , , . ORDER BY. SUM(goals_for) AS total_goals_tie .

- , . - :

SELECT r1.*, SUM(m1.points) AS total_points_tie
FROM (
    SELECT  team_id, 
            COUNT(match_id) AS total_matches,
            SUM(goals_for) AS total_goals_for, 
            SUM(points) AS total_points
    FROM    match_teams
    WHERE match_id IN (10,12,15,61,62,63,66,70,73,77)
    GROUP BY team_id
) r1
LEFT JOIN (
    SELECT team_id, SUM(points) AS total_points
    FROM  match_teams
    WHERE match_id IN (10,12,15,61,62,63,66,70,73,77)
    GROUP BY team_id
) r2 
    ON  r2.total_points = r1.total_points
    AND r2.team_id <> r1.team_id
LEFT JOIN (
    SELECT m1.team_id, m1.points, m2.team_id AS opponent_id
    FROM match_teams m1
    JOIN match_teams m2 ON m2.match_id = m1.match_id AND m2.team_id <> m1.team_id
    WHERE m1.match_id IN (10,12,15,61,62,63,66,70,73,77)
) m1
    ON  m1.team_id = r1.team_id
    AND m1.opponent_id = r2.team_id
GROUP BY r1.team_id
ORDER BY r1.total_points DESC, total_points_tie DESC, r1.total_goals_for DESC

, . , PHP :

$sub_match_teams = "
    SELECT m1.*, m2.team_id AS opponent_id
    FROM match_teams m1
    JOIN match_teams m2 ON m2.match_id = m1.match_id AND m2.team_id <> m1.team_id
    WHERE m1.match_id IN (10,12,15,61,62,63,66,70,73,77)
";

$sub_result = "
    SELECT  team_id, 
            COUNT(match_id) AS total_matches,
            SUM(goals_for) AS total_goals_for, 
            SUM(points) AS total_points
    FROM    ({$sub_match_teams}) m
    GROUP BY team_id
";

$final_query = "
    SELECT r1.*, SUM(m.points) AS total_points_tie
    FROM ({$sub_result}) r1
    LEFT JOIN ({$sub_result}) r2 
        ON  r2.total_points = r1.total_points
        AND r2.team_id <> r1.team_id
    LEFT JOIN ({$sub_match_teams}) m
       ON  m.team_id = r1.team_id
       AND m.opponent_id = r2.team_id
    GROUP BY r1.team_id
    ORDER BY r1.total_points DESC, total_points_tie DESC, r1.total_goals_for DESC
";

Answer 2

SQL, ...

-, .

. :

   select a.team_id, 
        sum(a.points) sort_criteria_1, /* points */
        (
           select sum(c.points)
              from match_teams c
              where c.team_id = a.team_id
                 and c.match_id in 
                    (
                       select d.match_id
                          from match_teams d
                          where d.team_id in
                             (
                                select e.team_id 
                                   from match_teams e
                                   where  e.team_id in (select f.team_id from match_teams f group by f.team_id having sum(f.points) = sum(a.points))
                                      and e.team_id <> a.team_id
                             )
                    )
        ) sort_criteria_2, /* points between */
        (
           select sum(c.goals_for)
              from match_teams c
              where c.team_id = a.team_id
                 and c.match_id in
                    (
                       select d.match_id
                          from match_teams d
                          where d.team_id in
                             (
                                select e.team_id 
                                   from match_teams e
                                   where  e.team_id in (select f.team_id from match_teams f group by f.team_id having sum(f.points) = sum(a.points))
                                      and e.team_id <> a.team_id
                             )
                    )
        ) sort_criteria_3 /* scores between */
  from match_teams a
  group by a.team_id
  order by sort_criteria_1 desc, sort_criteria_2 desc, sort_criteria_3 desc;

PS: , . , . SQL .

Answer 3

:

    team_id     total_points
    100         9
    200         6
    300         6
    400         6
    500         3

, .

SELECT 
  * 
FROM 
    (
     SELECT  
            team_id, 
            COUNT(match_id) AS total_matches,
            SUM(goals_for) AS total_goals_for, 
            SUM(points) AS total_points
     FROM    match_teams
    -- WHERE   match_id IN ([match IDs here])
     GROUP BY team_id
    ) q1 LEFT JOIN
    (
     SELECT  
            team_id as same_ranking_team_id, 
            SUM(points) AS total_points
     FROM    match_teams
     -- WHERE   match_id IN ([match IDs here])
     GROUP BY team_id
    )q2 on q1.total_points = q2.total_points and q1.team_id != q2.same_ranking_team_id

:

    team_id     total_points     total_points    same_ranking_team_id
    100         9                null            null
    200         6                6               300
    200         6                6               400
    300         6                6               200
    300         6                6               400
    400         6                6               200
    400         6                6               300
    500         3                null            null

,

    SELECT
       *
     FROM 
     (
     SELECT  
            team_id, 
            COUNT(match_id) AS total_matches,
            SUM(goals_for) AS total_goals_for, 
            SUM(points) AS total_points
     FROM    match_teams
    -- WHERE   match_id IN ([match IDs here])
     GROUP BY team_id
    ) q1 LEFT JOIN
    (
     SELECT  
            team_id as same_ranking_team_id, 
            SUM(points) AS total_points
     FROM    match_teams
     -- WHERE   match_id IN ([match IDs here])
     GROUP BY team_id
    )q2 on q1.total_points = q2.total_points and q1.team_id != q2.same_ranking_team_id
    LEFT JOIN
    (
        select 
             match_id,
             MIN(team_id) as first_team_id,
             MAX(team_id) as second_team_id
            from match_teams
         -- WHERE   match_id IN ([match IDs here])
            group by match_id
        ) q3 on LEAST(q1.team_id, q2.same_ranking_team_id) = q3.first_team_id and GREATEST(q1.team_id, q2.same_ranking_team_id) = q3.second_team_id

:

    team_id     total_points     total_points    same_ranking_team_id    match_id      frs_team     scd_team
    100         9                null            null                     null         null          null
    200         6                6               300                      62           200           300
    200         6                6               400                      63           200           400
    300         6                6               200                      62           200           300
    300         6                6               400                      70           300           400
    400         6                6               200                      63           200           400
    400         6                6               300                      70           300           400
    500         3                null            null                     null         null          null

now we need to add the points that team_id scored in this match, later you can also add goels

    SELECT
       *
     FROM 
     (
     SELECT  
            team_id, 
            COUNT(match_id) AS total_matches,
            SUM(goals_for) AS total_goals_for, 
            SUM(points) AS total_points
     FROM    match_teams
    -- WHERE   match_id IN ([match IDs here])
     GROUP BY team_id
    ) q1 LEFT JOIN
    (
     SELECT  
            team_id as same_ranking_team_id, 
            SUM(points) AS total_points
     FROM    match_teams
     -- WHERE   match_id IN ([match IDs here])
     GROUP BY team_id
    )q2 on q1.total_points = q2.total_points and q1.team_id != q2.same_ranking_team_id
    LEFT JOIN
    (
        select 
             match_id,
             MIN(team_id) as first_team_id,
             MAX(team_id) as second_team_id
            from match_teams
         -- WHERE   match_id IN ([match IDs here])
            group by match_id
        ) q3 on LEAST(q1.team_id, q2.same_ranking_team_id) = q3.first_team_id and GREATEST(q1.team_id, q2.same_ranking_team_id) = q3.second_team_id

        LEFT JOIN 
        (
          select 
             match_id,
             team_id ,
             points
          from match_teams
        -- WHERE   match_id IN ([match IDs here])
        )q4 on q1.team_id = q4.team_id and q3.match_id = q4.match_id

it should give you this

    team_id     total_points     total_points    same_ranking_team_id    match_id      frs_team     scd_team   team_id  match_id  points
    100         9                null            null                     null         null          null      null      null     null
    200         6                6               300                      62           200           300       200       62        3
    200         6                6               400                      63           200           400       200       63        3
    300         6                6               200                      62           200           300       300       62        0
    300         6                6               400                      70           300           400       300       70        3
    400         6                6               200                      63           200           400       400       63        0
    400         6                6               300                      70           300           400       400       70        0
    500         3                null            null                     null         null          null      null      null     null

Now you have all the data to classify the teams, the final query is as follows:

    SELECT
       q1.team_id,
       q1.total_points,
       sum(COALESCE(q4.points, 0)) as teis_points
    FROM 
     (
     SELECT  
            team_id, 
            COUNT(match_id) AS total_matches,
            SUM(goals_for) AS total_goals_for, 
            SUM(points) AS total_points
     FROM    match_teams
    -- WHERE   match_id IN ([match IDs here])
     GROUP BY team_id
    ) q1 LEFT JOIN
    (
     SELECT  
            team_id as same_ranking_team_id, 
            SUM(points) AS total_points
     FROM    match_teams
     -- WHERE   match_id IN ([match IDs here])
     GROUP BY team_id
    )q2 on q1.total_points = q2.total_points and q1.team_id != q2.same_ranking_team_id
    LEFT JOIN
    (
        select 
             match_id,
             MIN(team_id) as first_team_id,
             MAX(team_id) as second_team_id
            from match_teams
         -- WHERE   match_id IN ([match IDs here])
            group by match_id
        ) q3 on LEAST(q1.team_id, q2.same_ranking_team_id) = q3.first_team_id and GREATEST(q1.team_id, q2.same_ranking_team_id) = q3.second_team_id

        LEFT JOIN 
        (
          select 
             match_id,
             team_id ,
             points
          from match_teams
        -- WHERE   match_id IN ([match IDs here])
        )q4 on q1.team_id = q4.team_id and q3.match_id = q4.match_id

        group by q1.team_id, q1.total_points
        order by q1.total_points desc, teis_points desc

and it will give you

 

    team_id     total_points     teis_points  
    100         9                0
    200         6                6 
    300         6                3
    400         6                0
    500         3                0

Hope I haven’t missed anything here.

to check my answer:

    drop table match_teams;

    create table match_teams(
    match_id int,
    team_id int,
    goals_for int,
    points int
    );

    insert into match_teams values
    (10,100,2,3),(10,200, 1, 0),(12,100, 3, 3),
    (12,300, 0, 0),(15,100, 1, 0),(15,400, 2, 3),
    (61,100, 2, 3),(61,500, 0, 0),(62,200, 5, 3),
    (62,300, 1, 0),(63,200, 2 ,3),(63,400, 1 ,0),
    (66,200, 0 ,0),(66,500, 3 ,3),(70,300, 4 ,3),
    (70,400, 0 ,0),(73,300, 5 ,3),(73,500, 1 ,0),
    (77,400, 2 ,3),(77,500, 1 ,0);