SQL 2005 和内连接帮助

发布于 2024-08-15 03:42:19 字数 803 浏览 3 评论 0原文

我正在尝试内部连接 2 个临时表
我知道这是可以做到的，我以前做过，但我完全忘记了怎么做

请告诉我
下面是我尝试执行的查询

select tmp1.*, tmp2.cnt from
(
    select 
        1 as ClassificationType,
        tblMatches.IdGame,
        tblMatches.IdPlayer,
        sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
        count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    from 
        tblMatches  
    group by IdPlayer, IdGame   
) as tmp1
inner join (select IdWinner, count(IdWinner) as cnt from tblCompetitions where IdWinner = tmp1.IdPlayer) as tmp2 
            on tmp2.IdWinner = tmp1.IdPlayer

这将失败
我认为我不允许在创建 tmp2 的子查询中使用 tmp1

消息 4104，第 16 级，状态 1，第 17 行多部分标识符 “tmp1.IdPlayer”无法绑定。

原文

I am trying to inner join 2 temp tables
I know this can be done, I have done this before but i completely forgot how to do it

Please advise me
Below is the query that I try to execute

select tmp1.*, tmp2.cnt from
(
    select 
        1 as ClassificationType,
        tblMatches.IdGame,
        tblMatches.IdPlayer,
        sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
        count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    from 
        tblMatches  
    group by IdPlayer, IdGame   
) as tmp1
inner join (select IdWinner, count(IdWinner) as cnt from tblCompetitions where IdWinner = tmp1.IdPlayer) as tmp2 
            on tmp2.IdWinner = tmp1.IdPlayer

This will fail with
I think I am not allowed to use tmp1 in the subquery that create tmp2

Msg 4104, Level 16, State 1, Line 17
The multi-part identifier
"tmp1.IdPlayer" could not be bound.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谎言月老 2024-08-22 03:42:19

您不是试图连接两个临时表，而是两个派生表。

您无法在派生表外部访问其内部数据，除非该数据位于 SELECT 子句中。

请尝试以下操作：

select tmp1.*, tmp2.cnt from
(
    select 
        1 as ClassificationType,
        tblMatches.IdGame,
        tblMatches.IdPlayer,
        sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
        count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    from 
        tblMatches      
    group by IdPlayer, IdGame   
) as tmp1
inner join (select IdWinner, count(IdWinner) as cnt from tblCompetitions GROUP BY IdWinner) as tmp2 
                on tmp2.IdWinner = tmp1.IdPlayer

You are not trying to join two temp tables, but two derived tables.

You cannot access the inner data of one derived table in outside of it unless it's in the SELECT clause.

Try the following:

select tmp1.*, tmp2.cnt from
(
    select 
        1 as ClassificationType,
        tblMatches.IdGame,
        tblMatches.IdPlayer,
        sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
        count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    from 
        tblMatches      
    group by IdPlayer, IdGame   
) as tmp1
inner join (select IdWinner, count(IdWinner) as cnt from tblCompetitions GROUP BY IdWinner) as tmp2 
                on tmp2.IdWinner = tmp1.IdPlayer

回复收藏 0 原文

难忘№最初的完美 2024-08-22 03:42:19

select
    1 as ClassificationType,
    tmp1.IdGame,
    tmp1.IdPlayer,
    sum(tmp1.Score) as Score,
    sum(tmp1.Points) as Points,
    sum(tmp1.OpponentScore) as OpponentScore,
    count(tmp1.ID) as MatchesCount,
    count(distinct tmp1.IdCompetition) as CompetitionsCount,
    count(tmp2.IdWinner) as cnt
from 
    tblMatches tmp1
    inner join
    tblCompetitions tmp2
        on tmp2.IdWinner = tmp1.IdPlayer
group by
    tmp1.IdPlayer,
    tmp1.IdGame

select
    1 as ClassificationType,
    tmp1.IdGame,
    tmp1.IdPlayer,
    sum(tmp1.Score) as Score,
    sum(tmp1.Points) as Points,
    sum(tmp1.OpponentScore) as OpponentScore,
    count(tmp1.ID) as MatchesCount,
    count(distinct tmp1.IdCompetition) as CompetitionsCount,
    count(tmp2.IdWinner) as cnt
from 
    tblMatches tmp1
    inner join
    tblCompetitions tmp2
        on tmp2.IdWinner = tmp1.IdPlayer
group by
    tmp1.IdPlayer,
    tmp1.IdGame

回复收藏 0 原文

萌能量女王 2024-08-22 03:42:19

tmp2 中的 where 子句与连接条件重复：

inner join (select IdWinner, count(IdWinner) as cnt 
            from tblCompetitions 
            where IdWinner = tmp1.IdPlayer) as tmp2 
on         tmp2.IdWinner = tmp1.IdPlayer

只需删除 where 子句即可。此外，就像 Astander 在他现已删除的帖子中指出的那样，第二个查询也需要 group by：

inner join (select IdWinner, count(IdWinner) as cnt 
            from tblCompetitions
            group by IdWinner) as tmp2 
on         tmp2.IdWinner = tmp1.IdPlayer

您无法从子查询引用外部查询的原因是，这将使连接取决于连接的左侧部分。

The where clause in tmp2 duplicates the join condition:

inner join (select IdWinner, count(IdWinner) as cnt 
            from tblCompetitions 
            where IdWinner = tmp1.IdPlayer) as tmp2 
on         tmp2.IdWinner = tmp1.IdPlayer

Just remove the where clause. In addition, like Astander noted in his now deleted post, the second query needs a group by too:

inner join (select IdWinner, count(IdWinner) as cnt 
            from tblCompetitions
            group by IdWinner) as tmp2 
on         tmp2.IdWinner = tmp1.IdPlayer

The reason you can't reference the outer query from a subquery is that this would make the right part of the join depend on the left part of the join.

回复收藏 0 原文

初与友歌 2024-08-22 03:42:19

您实际上不需要第二个子查询。这又如何呢？

SELECT tmp1.ClassificationType, tmp1.IdGame, tmp1.IdPlayer,
       COUNT(tblCompletions.IdWinner) as cnt FROM
(
    SELECT 
        1 as ClassificationType,
        tblMatches.IdGame,
        tblMatches.IdPlayer,
        sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
        count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    FROM 
        tblMatches      
    GROUP BY IdPlayer, IdGame   
) as tmp1
INNER JOIN tblCompletions ON tmp1.IdPlayer = tblCompletions.IdWinner
GROUP BY tmp1.ClassificationType, tmp1.IdGame, tmp1.IdPlayer

You shouldn't actually need the second subquery. What about this?

SELECT tmp1.ClassificationType, tmp1.IdGame, tmp1.IdPlayer,
       COUNT(tblCompletions.IdWinner) as cnt FROM
(
    SELECT 
        1 as ClassificationType,
        tblMatches.IdGame,
        tblMatches.IdPlayer,
        sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
        count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    FROM 
        tblMatches      
    GROUP BY IdPlayer, IdGame   
) as tmp1
INNER JOIN tblCompletions ON tmp1.IdPlayer = tblCompletions.IdWinner
GROUP BY tmp1.ClassificationType, tmp1.IdGame, tmp1.IdPlayer

回复收藏 0 原文

~没有更多了~