分组取最值那条记录的id

小鱼儿 2021-05-12 11:55 459阅读 0赞

# 需求分析（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');

**表数据：**  
![这里写图片描述][70]  
**需求：**查询所有人的最高分的其他字段- - -查询不同userid最高的score的其他几个字段，比如column1字段。

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

**错误1：**直接分组取其他字段  
![这里写图片描述][70 1]  
**错误2：**直接分组记录id  
![这里写图片描述][70 2]  
**错误3：**直接分组记录id，不分组。- - - 这个有一点oracle基础的都知道是不行的0.0  
![这里写图片描述][70 3]  
**解决：**使用oracle中的函数：row\_number()over(partition by 字段1 order by 字段2 )  
![这里写图片描述][70 4]  
**分析：**简单来说这个函数就是根据字段1进行分组，然后再根据字段2进行排序，查询结果就是分组排序之后的序号。更让人兴奋的是，这个函数竟然在不分组的情况下，还可以查询其他字段。这样一来是不是就能满足我们刚才的需求了呢！  
![这里写图片描述][70 5]

# Mysql中： #

是否有一些热爱学习的或者思维严谨的小伙伴会想，mysql中也存在这种函数吗？？？这也可能是像我这种培训教的oracle的学生才会有的想法。。。因为培训那时候一直强调使用分组函数之后查询其他字段都需要group by；而mysql中使用分组函数之后查询其他字段并不需要group by一下。。。  
![这里写图片描述][70 6]  
![这里写图片描述][70 7]  
不过学习归学习，其实mysql中如果不能这样写也是有解决办法的！

## mysql中的group\_concat()函数 ##

**分析：**这个函数的作用就是分组拼接，group\_concat(字段1，order by 字段2)，即将字段2排序好之后把字段1用‘逗号分隔。那么有这个函数之后，离我们的目标就很近了，剩下的我们只需要将字段1变成id，将字段2变成score，然后对他进行根据userid分组就行，这样查出来的是不是就是这个userid所有记录的id，那么这个结果每条记录的第一个逗号隔开的id就是score最小的id，这样就能取到最值的id了。  
![这里写图片描述][70 8]  
**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

[70]: /images/20210511/e8f22a9012614efab60d45afb57fb5c6.png
[70 1]: /images/20210511/e50aa1d6df5d448cb57afee2f188a184.png
[70 2]: /images/20210511/c096d281bd2449bd8e70d282e1649357.png
[70 3]: /images/20210511/1856ec49bf1e4a298897969f6de6e41e.png
[70 4]: /images/20210511/654af850f3bd43409cfc9bce45942a85.png
[70 5]: /images/20210511/01684a45e1d44cfab81dd5866376c5c4.png
[70 6]: /images/20210511/40dce7213ba44e7891627be61dfc7651.png
[70 7]: /images/20210511/67ff5f86219d4ebd8625424f9763565c.png
[70 8]: /images/20210511/0282bec95195427ea5f60e326ad92348.png