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

前天, 看到"爱新觉罗.毓华"的一篇关于讨论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函数实现的办法. 我的讨论只是帮助大家开头思路, 并且将原有讨论情况进行扩充.