mysql - 使用 group by 的 SQL 选择查询

Question

如何当前获取TeamCount和TeamLead Name

  Teams | TeamCount | TeamLead Name
-------------------------------------
| Team1 | 2         | NULL
| Team2 | 2         | NULL
| Team1 | 1         | Prashanth

有时团队可能有也可能没有团队领导。所以我们只需要将 TeamLead 名称显示为空，如果找不到团队的团队负责人

我需要一些帮助才能脱身，如下所示

Teams | TeamCount | TeamLead Name
---------------------------------
Team1 | 3         | Prashanth
Team2 | 2         | NULL

Answer 1

给你:

MySQL:

select team as teamId, sum(teamcount) as teamcount,
  (select teamleadname from teams
   where teamId = teams.team and teamleadname is not null
   limit 1) as teamleadname
from teams
group by team

http://sqlfiddle.com/#!9/5889d/39

SQL 服务器:

declare @teams TABLE(Team varchar(20), TeamCount int, TeamLeadName varchar(20));

INSERT INTO @teams
VALUES
    ('Team1', 2, null),
    ('Team2', 2, null),
    ('Team1', 1, 'Prashanth')

select l.Team, l.TeamCount, r.TeamLeadName
from
    (select Team , sum(TeamCount) as TeamCount from @teams group by Team) as l 
    left outer join 
    (select Team, TeamLeadName 
    from 
        (select Team, TeamLeadName, row_number() over(partition by Team order by TeamLeadName desc) as roworder 
        from @teams) as o where o.roworder = 1
    ) as r
    on l.Team = r.Team

希望这对您有所帮助。