选择有固定奖杯数量和积分的球员

本文是小编为大家收集整理的关于选择有固定奖杯数量和积分的球员的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

我需要打印出拥有 2 个奖杯的球员，并且我需要将他们所有比赛的得分相加.我需要使用我的数据库中的 3 个表

1.我需要 trophy_count ，我可以通过这一步 ->

select surname, count(t.player_id) as trophy_count

from dbo.Players p 
left join Trophies t  on t.player_id=p.id

group by p.surname

所以，SQL 给了我这个

2.打印所有积分 ->

select name, sum(points) as points


from dbo.Players p
    inner join dbo.Stats s on s.player_id=p.id  

    group by p.name

SQL 给了我这个:

我想在一个查询中执行此操作:

select name, sum(points) as points, COUNT(t.player_id) as trophy_count


from dbo.Players p
    inner join dbo.Stats s on s.player_id=p.id  
    inner join dbo.Trophies t on t.player_id=p.id

    group by p.name

SQL 给了我这个，SQL 将我所有的信息乘以 x 2，这是错误的

而且，在这次失败时，我不知道我需要为那些拥有 2 个奖杯并总结他们所获得积分的玩家写什么.(朗佐球21分2奖杯，克里斯塔普斯波尔津吉斯17分2奖杯).

推荐答案

为了更安全的结果，将所有当前查询包装在子查询中.

SELECT p.id, p.name, p.surname, 
        IFNULL(trop.trophy_count, 0),
        IFNULL(pts.points, 0)
FROM dbo.Players p
    LEFT JOIN 
    (
        select p.id, count(t.player_id) as trophy_count
        from dbo.Players p 
                left join Trophies t  on t.player_id=p.id
        group by p.id
    ) trop ON p.id = trop.id
    LEFT JOIN
    (
        select p.id,sum(points) as points
        from dbo.Players p
            inner join dbo.Stats s on s.player_id = p.id    
        group by p.id
    ) pts ON p.id = pts.id

本文地址：https://www.itbaoku.cn/post/1556971.html

百度翻译此文有道翻译此文

问题描述

I need print out players who have 2 trophies, and I need sum their all matches earned points. There i need use 3 tables from my DB

1.I need got trophy_count , I can this with this step ->

select surname, count(t.player_id) as trophy_count

from dbo.Players p 
left join Trophies t  on t.player_id=p.id

group by p.surname

So, SQL gave me this

2.Print out all earned points ->

select name, sum(points) as points


from dbo.Players p
    inner join dbo.Stats s on s.player_id=p.id  

    group by p.name

SQL gave me this:

I want do this in one query:

select name, sum(points) as points, COUNT(t.player_id) as trophy_count


from dbo.Players p
    inner join dbo.Stats s on s.player_id=p.id  
    inner join dbo.Trophies t on t.player_id=p.id

    group by p.name

and SQL gave me this, SQL multiplies x 2 all my info, it is wrong

And , at this fail, I dont know what i need write, for select players who have 2 trophies and sum their earned points. (Lonzo ball 21 point 2trophies,Kristaps Porzingis 17points 2trophies).

推荐答案

For safer result, wrap all your current query in a subquery.

SELECT p.id, p.name, p.surname, 
        IFNULL(trop.trophy_count, 0),
        IFNULL(pts.points, 0)
FROM dbo.Players p
    LEFT JOIN 
    (
        select p.id, count(t.player_id) as trophy_count
        from dbo.Players p 
                left join Trophies t  on t.player_id=p.id
        group by p.id
    ) trop ON p.id = trop.id
    LEFT JOIN
    (
        select p.id,sum(points) as points
        from dbo.Players p
            inner join dbo.Stats s on s.player_id = p.id    
        group by p.id
    ) pts ON p.id = pts.id

上一篇：创建列表<customObject>从列表<string>创建

下一篇：LINQ多重连接在多个条件下加入