利用 GROUP BY 和 MAX 实现对表数据分组统计后的过滤查询（Oracle920）

        项目中遇到一个颇为复杂的数据库查询问题，想通之后又觉得挺简单的。其实还是对上学时就学过的 SQL 语言知识点不熟悉，比如 Group By。如果熟悉 SQL 语言，那么有很多筛选操作就可以放到数据库中完成，会大大减少客户端程序编写的代码量，也降低程序的复杂度。下面我举一个简单的例子，把分组查询的知识复习一下。

---

        假设表 TSalary 存储某项目组每个月的人员工资，该项目组人员流动性很强，某人可能 2 月在 3 月就不在了。

表结构如下：

--///////////////////////////////////////////////////////////////////////////////

SQL> desc TSalary
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(3)
 NAME                                      NOT NULL VARCHAR2(8)
 MONTH                                     NOT NULL NUMBER(6)
 SALARY                                    NOT NULL NUMBER(8,2)

SQL>

--///////////////////////////////////////////////////////////////////////////////

        其中 ID 列是用 sequence 实现的自增长主键列，月份 Month 用了数值型，格式为“200507”。

插入如下测试记录：

--///////////////////////////////////////////////////////////////////////////////

SQL> select * from TSalary;

 ID NAME   MONTH SALARY
--- ---- ------- ------
  1 张三  200501    701
  2 李四  200501    901
  3 王五  200501    801
  4 赵六  200501   1001
  5 张三  200502    702
  6 李四  200502    902
  7 王五  200502    802
  8 李四  200503    903
  9 王五  200503    803
 10 张三  200504    704

已选择10行。

SQL>

--///////////////////////////////////////////////////////////////////////////////

        查询需求：每个人最后拿到工资的那个月的工资记录。

解决方法：

--///////////////////////////////////////////////////////////////////////////////

SQL> select * from TSalary where id in (select max(id) from TSalary group by name);

 ID NAME   MONTH SALARY
--- ---- ------- ------
  4 赵六  200501   1001
  8 李四  200503    903
  9 王五  200503    803
 10 张三  200504    704

SQL>

--///////////////////////////////////////////////////////////////////////////////

        分析：先用 group by 把表数据按 Name 分组，然后用 max 函数统计出每组最大的 ID 号，此最大 ID 号集合对应的记录集即为所求结果。

---

知识点复习：
        Group By: 将查询结果按某一列或多列值分组，值相等的为一组。可以在 Group By 子句后添加 Having 子句限定选择组的条件，只有满足 Having 条件的组才会被选择出来。

        分组函数：运算每一组记录,每一组返回一个结果。
AVG ([DISTINCT|ALL]n)  求平均
COUNT ({ *|[DISTINCT|ALL]expr}) 计数
MAX ([DISTINCT|ALL]expr) 取最大
MIN ([DISTINCT|ALL]expr) 取最小
SUM ([DISTINCT|ALL]n)  求和

附：

数据库 Demo 脚本

CREATE TABLE TSalary (
ID NUMBER(3, 0) NOT NULL,
Name VARCHAR2(8) NOT NULL,
Month NUMBER(6, 0) NOT NULL,
Salary NUMBER(8, 2) NOT NULL,
CONSTRAINT PK_TSalary PRIMARY KEY (ID),
CONSTRAINT UQ_TSalary UNIQUE (Name, Month));

INSERT INTO TSalary VALUES( 1, '张三', 200501, 701 );
INSERT INTO TSalary VALUES( 2, '李四', 200501, 901 );
INSERT INTO TSalary VALUES( 3, '王五', 200501, 801 );
INSERT INTO TSalary VALUES( 4, '赵六', 200501, 1001 );
INSERT INTO TSalary VALUES( 5, '张三', 200502, 702 );
INSERT INTO TSalary VALUES( 6, '李四', 200502, 902 );
INSERT INTO TSalary VALUES( 7, '王五', 200502, 802 );
INSERT INTO TSalary VALUES( 8, '李四', 200503, 903 );
INSERT INTO TSalary VALUES( 9, '王五', 200503, 803 );
INSERT INTO TSalary VALUES( 10, '张三', 200504, 704 );