首页 文章

从“分组依据”中排除列

提问于
浏览
0

我有一张这样的桌子

我目前的查询

Select team, 
       stat_id, 
       max(statsval) as statsval 
from tbl 
group by team,
      statid

问题:我需要在选择中获得赛季而且不经意地我需要添加到分组但是是给我非预期的结果我不能改变我的分组 . 因为我需要按stat_id分组我只能分组赛季 . 我需要获得max()记录的季节 . 有人可以帮我吗?

我甚至试过了

Select team, 
       stat_id, 
       max (seasonid),
       max(statsval) as statsval 
from tbl 
group by team,
         statid

但它需要最大的季节不完全正确的结果 .

例外结果

+--------+--------+-------+---------+---------+
| season |  team  | round | stat_id | statval |
+--------+--------+-------+---------+---------+
|   2004 | 500146 |     3 |       1 |       5 |
|   2007 | 500147 |     1 |       1 |       4 |
+--------+--------+-------+---------+---------+

4 回答

  • 0

    使用Windows函数尝试此操作

    Select distinct team, 
                    statid, 
                    max(statsval) OVER(PARTITION BY team,statid ORDER BY seasonid) as statid, 
                    max(seasonid) OVER(PARTITION BY team,statid ORDER BY statid)
    from tbl
    
  • 0

    尝试此操作并在分组完成后查找团队ID:

    ;with tmp as 
    (
      select team, 
             stat_id, 
             max(statsval) as statsval 
      from tbl 
      group by team,
               statid
    ) 
    select tmp.*, 
           tbl.seasonid 
    from tmp join tbl 
    on tmp.team = tbl.team and  tmp.statid = tbl.stat_id;
    
  • 0

    如果你想要完整的行,你可以简单地使用相关的子查询:

    Select t.*
    from tbl t
    where t.season = (select max(t2.season) 
                      from tbl t2
                      where t2.team = t.team and t2.statsval = t.statsval
                     );
    

    索引在 tbl(team, statsval, season) 上,这可能与其他选项一样好或更好 .

    性能较差的有趣方法(即使使用索引)是:

    select top (1) with ties t.*
    from tbl t
    order by row_number() over (partition by team, statsval order by season desc);
    
  • 0

    根据您的SQL Server版本,这可以仅使用Window函数完成:

    SELECT DISTINCT  team
        , stat_id
        , max(statsval) OVER (PARTITION BY team, stat_id) statsval
        , FIRST_VALUE(season_id) OVER (PARTITION BY team, stat_id ORDER BY  statsval DESC)
    FROM tbl
    

相关问题