# 选择有固定奖杯数量和积分的球员[英] Select players with fixed trophy count and points

### 问题描述

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
```

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，这是错误的

## 推荐答案

```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
```

### 问题描述

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
```