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

问题描述

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

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.我需要trophy_count，我可以通过此步骤->

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

因此，SQL为此给了我

So, SQL gave me this

2.打印所有赚取的积分->

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给了我这个:

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

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

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

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

而且，在这次失败中，我不知道我需要写什么，对于那些拥有2个奖杯并累加其获分的精选球员. (Lonzo球21点2座奖杯，Kristaps Porzingis 17点2座奖杯).

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

这篇关于选择具有固定奖杯数和积分的球员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！