针对"按某一字段分组取最大(小)值所在行的数据"一文的深入讨论和研究

前天, 看到"爱新觉罗.毓华"的一篇关于讨论SQL查询的帖子: 按某一字段分组取最大(小)值所在行的数据

这篇文章讨论了七个查询情况, 除了第7部分外内容外, 其余的介绍的比较详细.  我继续针对第7种查询进行深入的讨论.

我们首先来看一下我们需要研究的数据情况:

create   table  tb(name  varchar ( 10 ),val  int ,memo  varchar ( 20 ))
insert   into  tb  values ( ' a ' ,     2 ,    ' a2(a的第二个值) ' )
insert   into  tb  values ( ' a ' ,     1 ,    ' a1--a的第一个值 ' )
insert   into  tb  values ( ' a ' ,     1 ,    ' a1--a的第一个值 ' )
insert   into  tb  values ( ' a ' ,     3 ,    ' a3:a的第三个值 ' )
insert   into  tb  values ( ' a ' ,     3 ,    ' a3:a的第三个值 ' )
insert   into  tb  values ( ' b ' ,     1 ,    ' b1--b的第一个值 ' )
insert   into  tb  values ( ' b ' ,     3 ,    ' b3:b的第三个值 ' )
insert   into  tb  values ( ' b ' ,     2 ,    ' b2b2b2b2 ' )
insert   into  tb  values ( ' b ' ,     4 ,    ' b4b4 ' )
insert   into  tb  values ( ' b ' ,     5 ,    ' b5b5b5b5b5 ' )

我们首先再来确认一下我们要做什么事情. 只有在清楚我们需要做什么事情的情况下, 我们才能得到正确的结论. 我们要根据某一个字段(例如: name), 进行分组, 并在分组的基础上, 选出某列(例如: val)上的最值. 并最终返回name, val值在表tb中所有字段信息.

由于表tb上没有主键, 因此name, val, memo这个三元组, 不一定能从表tb中返回唯一的元组. 由于我们要根据name进行分组, 并选出val上的最值, 我们可以发现name, val的二元组, 也不一定从表tb中返回唯一的元组.

根据"爱新觉罗.毓华"设计的用例(testcase)来看, 并没有考虑当name, val相同时, memo不同时应该返回何种结果. 通过"爱新觉罗.毓华"给出的求解方案, 我们知道无论memo为何值, 利用临时表或row_number都只返回"第一个"元组.

我们已经确认了"爱新觉罗.毓华"这位朋友的文章中第七种问题的情况, 我们除了要完成"爱新觉罗.毓华"已经完成的操作(问题1). 还要实现一个能够对不同memo返回多条结果的SQL语句(问题2). 实际上问题2比问题1要容易得多.

针对问题1来说, "爱新觉罗.毓华"给出2种方法分别利用:

1. 临时表+自增列;

2. row_number函数;

我们是否还能给出第三种方法呢? 在继续讨论问题1之前, 我们先来解决问题2, 问题2更加简单. 我们首先追加一些数据:

insert   into  tb  values ( ' a ' ,     1 ,    ' 问题2 ADD BY Edengundam ' )
insert   into  tb  values ( ' b ' ,     1 ,    ' 问题2 ADD BY Edengundam ' )

我们只对a, b加上两条数据, 来补充一下测试用例(我们不再讨论NULL值问题).

我们首先根据name字段进行分组, 并取出val中最小值:

SELECT  name,  MIN (val)  AS  minval  FROM  tb  GROUP   BY  name;

输出结果:

name       minval
---------- -----------
a          1
b          1

(2 row(s) affected)

接下来, 我们只需要利用 "相关子查询" 或者 "JOIN" 来选出最小值对应的元组:

SELECT   DISTINCT  tb.name, tb.val, tb.memo
FROM
tb,
( SELECT  name,  MIN (val)  AS  minval  FROM  tb  GROUP   BY  name)  AS  tmp
WHERE  tb.name  =  tmp.name  AND  tb.val  =  tmp.minval;

输出结果:

name       val         memo
---------- ----------- ----------------------------------------
a          1           a1--a的第一个值
a          1           问题2 ADD BY Edengundam
b          1           b1--b的第一个值
b          1           问题2 ADD BY Edengundam

(4 row(s) affected)

注意, 这里的DISTINCT用来将重复的a, 1, 'a1--a的第一个值'列消去. 现在我们已经完成了问题2, 下面我们需要思考如何能够不依赖临时表和row_number这类函数来针对不同的name, MIN(val)返回一条元组.

我们应该从tb表中选择出name, MIN(val)的列, 并且这一列的memo是通过TOP语句唯一选择的. 这就是我们的思路, 利用相关子查询, TOP语句:

SELECT   DISTINCT  tb.name, tb.val, tb.memo
FROM
tb,
( SELECT  name,  MIN (val)  AS  minval  FROM  tb  GROUP   BY  name)  AS  tmp
WHERE  tb.name  =  tmp.name
     AND  tb.val  =  tmp.minval
     AND  tb.memo  =  ( SELECT   TOP   1  memo  FROM  tb  AS  d  WHERE  tb.name  =  d.name  AND  tb.val  =  d.val);

我们看看输出结果:

name       val         memo
---------- ----------- ----------------------------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值

(2 row(s) affected)

我们已经完成任务了, 但是我必须提醒大家效率上来说, 最好的是"爱新觉罗.毓华"利用row_number函数实现的办法. 我的讨论只是帮助大家开头思路, 并且将原有讨论情况进行扩充.