分组取最值那条记录的id

需求分析（oracle中）

不知道各位小伙伴有没有这种需求，就是想获取一下某某某的最新消息。这句话的意思就是先根据userid分组，然后取时间的最大值，那么这样一来，我们就只能取到userid和时间两个字段，并不能取到最新这条记录的id，也就是最新这条记录的其他字段我们没法取到（如果我们要查询其他字段就必须一起分组，那样就会存在一个userid多条记录，那就不是最新的消息了）。。。。那么怎么办呢？？？

---

实例

sql脚本：

CREATE TABLE "MYTEST" (
"ID" VARCHAR2(32 BYTE) NULL ,
"USERID" VARCHAR2(32 BYTE) NULL ,
"SCORE" NUMBER NULL ,
"COLUMN1" VARCHAR2(255 BYTE) NULL ,
"COLUMN2" VARCHAR2(255 BYTE) NULL ,
"COLUMN3" VARCHAR2(255 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE
;
INSERT INTO "MYTEST" VALUES ('e6df2310985240eab011cfa74ff6cefe', 'a', '70', '小明', '123', '20');
INSERT INTO "MYTEST" VALUES ('52d0a1e4db9444bcb821cbe2b7fe7a41', 'a', '40', '小黄', '234', '19');
INSERT INTO "MYTEST" VALUES ('8efadf9822aa42e7a62ad5f562beb9b5', 'a', '90', '小白', '345', '30');
INSERT INTO "MYTEST" VALUES ('2534af8ebc55481d9889e9d59ddf8d0f', 'b', '60', '小工', '456', '54');
INSERT INTO "MYTEST" VALUES ('cf2cb80380674808a958f126f5cf8bc2', 'b', '80', '小红', '567', '6');
INSERT INTO "MYTEST" VALUES ('1d73e080b2dd4eb9bd4164f40f79ff50', 'b', '70', '小吕', '678', '35');

表数据：

需求：查询所有人的最高分的其他字段- - -查询不同userid最高的score的其他几个字段，比如column1字段。

分析：显然直接用group by这个函数的是不行的，他只能查出userid和score这两个字段，这并不能让我们唯一确定一条记录，我们的最终目的其实就是为了获取这个id，只要能拿到id，什么都变得简单了。

错误1：直接分组取其他字段

错误2：直接分组记录id

错误3：直接分组记录id，不分组。- - - 这个有一点oracle基础的都知道是不行的0.0

解决：使用oracle中的函数：row_number()over(partition by 字段1 order by 字段2 )

分析：简单来说这个函数就是根据字段1进行分组，然后再根据字段2进行排序，查询结果就是分组排序之后的序号。更让人兴奋的是，这个函数竟然在不分组的情况下，还可以查询其他字段。这样一来是不是就能满足我们刚才的需求了呢！

Mysql中：

是否有一些热爱学习的或者思维严谨的小伙伴会想，mysql中也存在这种函数吗？？？这也可能是像我这种培训教的oracle的学生才会有的想法。。。因为培训那时候一直强调使用分组函数之后查询其他字段都需要group by；而mysql中使用分组函数之后查询其他字段并不需要group by一下。。。


不过学习归学习，其实mysql中如果不能这样写也是有解决办法的！

mysql中的group_concat()函数

分析：这个函数的作用就是分组拼接，group_concat(字段1，order by 字段2)，即将字段2排序好之后把字段1用‘逗号分隔。那么有这个函数之后，离我们的目标就很近了，剩下的我们只需要将字段1变成id，将字段2变成score，然后对他进行根据userid分组就行，这样查出来的是不是就是这个userid所有记录的id，那么这个结果每条记录的第一个逗号隔开的id就是score最小的id，这样就能取到最值的id了。

SUBSTRING_INDEX函数：这个函数应该不陌生吧，简单来说就根据什么分割之后取第几个，相当于java中的split(“,”)函数，然后再取第几个。。。

总结：

在oracle中如果需要某人的最值那条记录的id就是用row_number()over()函数，mysql中直接使用max()即可。。。其实我内心是很不愿意接受的，因为搞了好久才搞出来的。。。然后他们告诉我mysql分组函数查询其他字段不需要group by。。。

扩展：

oracle去掉重复的数据- - ->根据rowid查询最值- - ->获取最值的id，然后删除其他的

select a.* from MYTEST a
where 1=1
and (a.userid,rowid) in(
    select b.userid,max(rowid) from MYTEST b
    where 1=1  
    having count(*)>1
    group by  b.userid    
)
order by rowid