Sqlserver——查询技巧/优化方案——根据多个字段分组,后取出分组中按多个列排序的最大值记录

          文字描述起来有点抽象，具体的先看示例，相信很多人碰到过类型的场景

                      SELECT    'A' AS ii ,
                                1 AS i1 ,
                                1 AS i2
                      UNION ALL
                      SELECT    'A' AS ii ,
                                3 AS i1 ,
                                2 AS i2
                      UNION ALL
                      SELECT    'A' AS ii ,
                                2 AS i1 ,
                                1 AS i2
                      UNION ALL
                      SELECT    'A' AS ii ,
                                2 AS i1 ,
                                1 AS i2
                      UNION ALL
                      SELECT    'B' AS ii ,
                                1 AS i1 ,
                                1 AS i2
                      UNION ALL
                      SELECT    'B' AS ii ,
                                1 AS i1 ,
                                3 AS i2
                      UNION ALL
                      SELECT    'B' AS ii ,
                                2 AS i1 ,
                                1 AS i2
                      UNION ALL
                      SELECT    'B' AS ii ,
                                2 AS i1 ,
                                1 AS i2

假设这是目标结果集，查询出来的结果如下

现在我想要查出红框中的这条记录

也就是需要按照 ii 列分组,同时找到这个每个分组下面 先按照i1、再按照i2的排序方式下，拿出最大的那条记录

例如: A     1      4 

         A     2      1

符合条件的应该是 A   2   1 这条记录

如果才能写出一个符合的查询呢：

第一反应可能会这样写-->写按照 ii 列分组取一次 max(i1)

                                    -->然后拿到结果集再进行一次自连接，按照 ii列 和i1列分组 再取出max(i2) ,这样就能拿到符号条件的值

这个方法有个缺陷，会涉及到多次聚合,并且，如果需要排序的列数目增多的话，会进行非常多次的自连接和聚合！

下面介绍另外一种方式(需要用到开窗函数)，可以用固定的查询方式，不管需要按照多少列排序都能用类似的语句查询

SELECT  *
FROM    ( SELECT    AB.ii ,
                    AB.i1 ,
                    AB.i2 ,
                    AB.ids ,
                    MAX(AB.ids) OVER ( PARTITION BY AB.ii ) AS idss
          FROM      ( SELECT    AA.ii ,
                                AA.i1 ,
                                AA.i2 ,
                                DENSE_RANK() OVER ( ORDER BY AA.ii, AA.i1, AA.i2 ) AS ids
                      FROM      ( SELECT    'A' AS ii ,
                                            1 AS i1 ,
                                            1 AS i2
                                  UNION ALL
                                  SELECT    'A' AS ii ,
                                            3 AS i1 ,
                                            2 AS i2
                                  UNION ALL
                                  SELECT    'A' AS ii ,
                                            2 AS i1 ,
                                            1 AS i2
                                  UNION ALL
                                  SELECT    'A' AS ii ,
                                            2 AS i1 ,
                                            1 AS i2
                                  UNION ALL
                                  SELECT    'B' AS ii ,
                                            1 AS i1 ,
                                            1 AS i2
                                  UNION ALL
                                  SELECT    'B' AS ii ,
                                            1 AS i1 ,
                                            3 AS i2
                                  UNION ALL
                                  SELECT    'B' AS ii ,
                                            2 AS i1 ,
                                            1 AS i2
                                  UNION ALL
                                  SELECT    'B' AS ii ,
                                            2 AS i1 ,
                                            1 AS i2
                                ) AA
                    ) AB
        ) AC
WHERE   AC.ids = AC.idss;

查询结果

先解释一下为什么会出现两条 B  2  1  -->因为按照排序规则来看，这两条记录是属于同一级别的数据，所以都会都被查出来

现在解释一下这个查询的逻辑：

首先-->对基础表AA 使用开窗函数 DENSE_RANK()(PS:具体用法，百度，这里不做赘述)

           按照你需要的列进行排序(这里是按照ii i1 i2 ，实际情况自己可以选择多列) 并 as成新列 ids

然后-->对第一步查出来的结果集再进行一次开窗函数MAX()Over() 查询，筛选出每个分组下的最大ids的值 并as 成新列 idss

最后-->对上一步的结果集做一次子查询，where 条件为 ids=idss 

这样查出来的就是我们所需要的满足条件的数据