mysql - SQL 不同元素按相同元素计入一组

Question

我在按此元素计算组中的不同元素时遇到问题。

让我解释一下，我有两个表:

tb1
team  amount       
1     90                  
2     80                  
3     70                  
4     50                  
5     60

tb2   
team player 
5    1
1    1
3    2
1    2
2    2
1    3
3    3
4    3
5    3
2    4

预期的结果是:

 player  nb_team Sum_amount nb_player
   1       2        150       3
   2       3        240       4
   3       4        270       3
   4       1        80        2

我这样做:

SELECT tb2.player, COUNT(DISTINCT tb1.team) as nb_team,
SUM(tb1.amount) AS sum,
(SELECT COUNT(DISTINCT tb2.player) 
FROM tb2 where tb1.team=tb2.team) AS nb_player
FROM tb1, tb2
WHERE tb1.team=tb2.team
GROUP BY tb2.player
ORDER BY tb2.player ASC;

前 3 列是正确的，但我无法获得 nb_player 的正确值。

我要统计球队的数量包括多少球员

例如第一行结果:

玩家 1 在 2 支队伍中比赛，总共有 3 名玩家(玩家#1、#2 和#3)

有什么想法吗？

Answer 1

统计一个球员的球队数量并将这些球队的数量相加需要一个不同的结果集，而不是统计与同一球队中的给定球员一起比赛的球员。所以我建议使用两个不同的子查询，然后在播放器上加入它们。

SELECT teams_total.player, teams_total.nb_team, teams_total.`sum`, 
       players_total.nb_players
FROM
( SELECT tb2.player, COUNT(DISTINCT tb1.team) as nb_team,
  SUM(tb1.amount) AS `sum` 
  FROM tb1 JOIN tb2 ON tb1.team=tb2.team
  GROUP BY tb2.player ) teams_total
JOIN
( SELECT tb2_1.player, COUNT(DISTINCT tb2_2.player) as nb_players
  FROM tb2 tb2_1
  JOIN tb1 ON tb2_1.team=tb1.team
  JOIN tb2 tb2_2 ON tb2_2.team=tb1.team
  GROUP BY tb2_1.player ) players_total
ON teams_total.player=players_total.player
ORDER BY teams_total.player ASC;