MySQL - 嵌套选择中的引用主查询(需要嵌套)

Question

我一直在寻找这个问题，并且总是得到改变做事方式的答案，但没有解决一般问题，即:在嵌套 SELECT 中引用主查询变量。

我需要它是一个嵌套查询，因为数据结构非常复杂。让我们看看我需要完成的示例:

SET @teamg_id := 0;
SELECT 
    @teamg_id := player.team_id,
    player.team_id AS player_team, 
    event.local_team_id AS local_team, 
    event.visitor_team_id AS visitor_team, 
    action.unique_id AS action_id, 
    action.player_id AS player_id,
    player.name AS player_name,
    playerindex.rank AS player_pos, 
    playerindex.ses_var_nest AS ses_var_nest 
FROM  
    er_players player, 
    er_events event, 
    er_actions action 
LEFT JOIN 
    (SELECT 
        unique_id, team_id, @pn:=@pn+1 AS rank, @teamg_id AS ses_var_nest  
    FROM 
        er_players, (SELECT @pn:=-1) p2 
    WHERE 
        team_id = @teamg_id 
    ORDER BY 
        dorsal ASC, id DESC) playerindex 
ON 
    playerindex.unique_id = action.player_id 
WHERE 
    action.unique_id = '1374572622'  
    AND player.unique_id = action.player_id 
    AND event.unique_id = (SELECT event_id 
        FROM er_analysis 
        WHERE unique_id = action.analysis_id)

如您所见，我需要返回该球员在其球队中的“索引”，按照查询返回的顺序(排名值)，但我需要将其与球队相关进行计数，而不是整个 Players 表。

为了做到这一点，我需要在嵌套 SELECT 中引用主查询值“player.team_id” - 我尝试过但无法做到，所以我最终使用了 session 变量。

我从这个查询中得到的是 @teamg_id 在主查询中很好地存储了值，但是当传递给嵌套查询时，它的值变成 NULL:

@teamg_id  |  player_team  |  local_team  |  visitor_team  |  action_id   | player_id  |  player_name  |  player_pos  |  ses_var_nest

1374570040 |   1374570040  |  1374570040  |  1374571827    |  1374572622  | 1374570146 |   Player name |     NULL     |     NULL

我的问题是如何在嵌套查询中使用 session 变量(我认为应该有效)，或者如何引用其中的主查询值。

感谢您的帮助!

编辑:这是 er_players 表的结构，这就是给我带来问题的结构。如果我说“给我‘玩家 3’的索引”，它应该给我 2 (0, 1, 2)，因为这是他团队中的第三位玩家。

+------------+------------+----------+----------+--------+
| unique_id  |  team_id   |   name   | position | dorsal |
+------------+------------+----------+----------+--------+
| 1374570066 | 1374570040 | Player 1 | Defense  |      1 |
| 1374570034 | 1374570040 | Player 2 | Defense  |      2 |
| 1374677119 | 1374571827 | Player 7 | Position |      7 |
| 1374570146 | 1374570040 | Player 3 | Attack   |      5 |
+------------+------------+----------+----------+--------+

编辑 2(解决方案):由于 Kordirko 的回答和一些演奏，找到了解决方案。

 SELECT 
        player.team_id AS player_team, 
        event.local_team_id AS local_team, 
        event.visitor_team_id AS visitor_team, 
        action.unique_id AS action_id, 
        action.player_id AS player_id,
        player.name AS player_name,
        playerindex.rank AS player_pos, 
        playerindex.ses_var_nest AS ses_var_nest 
    FROM  
        er_actions action 
    LEFT JOIN er_events event 
        ON event.unique_id = (SELECT event_id FROM er_analysis WHERE unique_id = action.analysis_id) 
    LEFT JOIN er_players player 
        ON player.unique_id = action.player_id 
    LEFT JOIN 
        (SELECT 
            unique_id, team_id, team_id AS ses_var_nest ,
            case when team_id <> @lastteam
                then  (@pn:=-1)
            end AS hander, 
            case when team_id <> @lastteam
                then(@lastteam:=team_id) 
            end AS team_id_new, 
            @pn:=@pn+1 AS rank 
        FROM 
            er_players, (SELECT (@pn:=-1),(@lastteam:=0)) p2 
        ORDER BY 
            team_id, dorsal ASC, id DESC) playerindex 
    ON 
        playerindex.unique_id = action.player_id  
        AND playerindex.team_id = player.team_id   /* new join condition */     
    WHERE 
        action.unique_id = '1374572622'

选择CASES后的@rank使其不两次为0，并重置选择前的@rank。否则，就变成0，而是变成每队的第二名球员。

希望有人能发现这个问题有帮助。

再次感谢 Kordirko 的宝贵时间和回答。

Answer 1

这行不通。
SELECT 子句始终在执行链的末尾进行评估，
@teamg_id 变量在嵌套子查询中将始终为 0。

尝试这个查询，它没有经过测试(我看不到你的数据，使用示例数据总是有帮助)，但它可能会给出所需的结果。
我注释掉了一些部分并添加了一个连接条件。

  /*  SET @teamg_id := 0; */
    SELECT 
        /* @teamg_id := player.team_id, */
        player.team_id AS player_team, 
        event.local_team_id AS local_team, 
        event.visitor_team_id AS visitor_team, 
        action.unique_id AS action_id, 
        action.player_id AS player_id,
        player.name AS player_name,
        playerindex.rank AS player_pos, 
        playerindex.ses_var_nest AS ses_var_nest 
    FROM  
        er_players player, 
        er_events event, 
        er_actions action 
    LEFT JOIN 
        (SELECT 
            unique_id, team_id, @pn:=@pn+1 AS rank, team_id AS ses_var_nest ,
            case when team_id <> @lastteam
                 then  (@pn:=-1) <> (@lastteam:=team_id) end
        FROM 
            er_players, (SELECT (@pn:=-1),(@lastteam:=0)) p2 
     /*   WHERE 
            team_id = @teamg_id */
        ORDER BY 
            team_id, dorsal ASC, id DESC) playerindex 
    ON 
        playerindex.unique_id = action.player_id  
        AND playerindex.team_id = player.team_id /* new join condition */     
    WHERE 
        action.unique_id = '1374572622'  
        AND player.unique_id = action.player_id 
        AND event.unique_id = (SELECT event_id 
            FROM er_analysis 
            WHERE unique_id = action.analysis_id)