取数据库中分组后每一个人的第一条数据

最新推荐文章于 2025-02-06 09:55:40 发布
转载 最新推荐文章于 2025-02-06 09:55:40 发布 · 2.8k 阅读 · 2

本文介绍了一种在 Oracle 数据库中提取特定数据的方法，利用 row_number() 函数配合子查询实现按指定条件选取记录。此外还对比了使用 rank() 函数的情况，并提供了 SQL Server 下类似操作的示例。

转载自：http://www.itpub.net/thread-1736695-1-1.html

如图


取出黄色标记的数据


解决方法：（在oracle成功）

select t.name1,t.name2,t.zx 
from (select row_number()over(partition by name1 order by zx desc)rn, test.* from test)t
where t.rn=1;

用rank的话你zx有值相等的，还是用row_number好了

法：

查询其他的：

sqlServer:类似的情况（未测试）

select * from t1 t where id =  (select top 1 id from t1 where id = t.id order by createtime desc)


大数据面试SQL（十二）：查询每个用户的第一条和最后一条记录
Lansonli（蓝深李）的博客
08-15 980
这里需要第一条和最后一条，因为无法提前预知每个用户的行数，所以使用两次row_number进行开窗，排序方式根据时间进行正向排序和逆向排序，分别取出行号为1的数据。现有一张订单表 t3_order 有订单ID、用户ID、商品ID、购买商品数量、购买时间，请查询出每个用户的第一条记录和最后一条记录。使用row_number()根据用户进行分组，根据时间分别进行正向排序和逆向排序，增加两个行号，分别为asc_rn和desc_rn。条件判断为asc_rn=1取第一条，desc_rn=1 取最后一条。
详解如何优雅实现先分组再组内排序取数据解决方案
weixin_41821642的博客
02-06 1085
分组后组内排序取第一条记录的操作，是 SQL 查询中的高频需求。推荐优先使用窗口函数，其次是子查询方法，并根据实际业务场景选择合适的实现方式。同时，结合索引和字段优化，可以显著提升查询性能。当然这是针对大批量查询的情况下，如果你这是查询一两个姓名的最小年纪这种，你完全可以查询出所有数据，在Java代码逻辑中利用内存分组之后再排序，取相应数据即可。
取分组后的第一条数据
Raqsoft
10-14 424
但是，目前的数据库对窗口函数实现程度各有不同，有些根本就没有实现。如果按照 SQL92 的标准，得用 JOIN 拼出窗口函数中序号的效果，难度就很大了。如果按照 SQL2003 的标准，那么使用窗口函数可以解决你的问题，即：按 EmpNum 分组，按规则取出每组第一条，规则可以是：组内序号，某个字段的排序位置。集算器提供 JDBC 接口与 JAVA 程序或报表工具集成，与使用数据库差不多，详情可参考。更复杂的情况还可参考【
达梦数据库，分组查询多个字段group by 分组问题
tsx668899的博客
12-29 6683
达梦数据库，分组查询多个字段group by 分组问题 ： select d.cur_code, d.cur_date, d.DIRE_RATE from (
多个数据分组选取其中第一条数据
DFL_always的专栏
12-02 2320
多个数据分组选取其中第一条数据背景： 选取同一个客户的首次记录的时间，即一个客户有不同时间的操作记录，我们只需要选取该客户的第一条数据。1、准备数据 insert into yliu(1,20,10); insert into yliu(id,time,card) values(1,20,10); insert into yliu(id,time,card) values(2,20,10);
oracle 查询分组的第一条数据
学习笔记
09-04 324
SELECT * FROM ( SELECT ROW_NUMBER () OVER ( PARTITION BY s.SEWAGENAME ORDER BY s.MARK_DATE DESC ) rn, ...
MySQL查询分组内最新的第一条数据
echola_mendes的博客
08-15 813
由于MySQL 5不支持窗口函数，因此不能使用PARTITION()、ROW_NUMBER()，获取每个班级中最新注册的学号人员信息也就是从 user_info 学生表中获取每个 class_id 班级的最新一条记录。
第一章：初识数据库(3)：数据库的语言分类
12-14
数据库是存储和管理数据的核心工具，对于初学者或者想要深入理解数据库的人来说，了解其基本概念、功能以及语言分类至关重要。本章将聚焦于数据库的语言分类，主要探讨的是SQL Server中的Transact-SQL（T-SQL），它...
数据库系统实用教程课件 06数据库中的数据交换.ppt
01-21
示例代码中展示了如何声明和打开一个游标，例如`agent_dollars`，它用于根据客户ID分组查询订单总额，这样用户可以按需遍历结果集。 总结来说，数据库中的数据交换涉及到一系列管理和控制机制，以确保数据的正确...
分组排序取第一条数据 SQL写法
程序猿的日常笔记
04-24 6338
在数据库查询过程中经常遇到需要分组排序查询第一条数据的情况。例如，在消息列表中需要展示每个联系人最近的一条信息。
达梦数据库分组排序获取最新数据
weixin_38670912的博客
05-28 1521
【代码】达梦数据库分组排序获取最新数据。
mysql分组之后得到各组排序后的第一条数据
sssssscccccc的博客
02-28 1096
记录一下自己在查询中的问题 SELECT FROM_UNIXTIME( t.create_time / 1000 ), t.* FROM ( SELECT u.* FROMuser_unlock_btb_detail u ORDER BY u.create_time DESC, u.id DESC LIMIT 100 ) t GROUP BY t.user_...
分组查询后得到每组中的第一条数据
奋斗、
09-13 7035
表clgl_clbzqk　　xh,dwxh,clbzhdrq xh表示　序号　 dwxh表示单位序号 clbzhdrq表示核定时间 xh是主键，目的是按dwxh分组，然后得到最近的一次记录 但是group by 不能完成此功能 可以用下面方法： select * from clgl_clbzqk x where xh = (select top 1 xh fr
db2取第一条数据
热门推荐
milife2012的专栏
12-20 1万+
select * from test.employee fetch first 1 rows only
人大金仓、PostgreSQL使用GROUP BY聚合后，取第一个值或最后一个值的办
chenliup的博客
03-13 1481
在PostgreSQL中，经常遇到使用Group By进行分类统计的SQL。在前面可以使用一些聚合函数SUM、COUNT、MAX、MIN这些。在一些特殊场景一下，比如有些字段需要取Group By后结果里的第一个或者最后一个。这个时候Pg里没有直接支持的函数，可以通过自定义函数的方式来实现。
SQL 分组（分区）排序获取第一条数据 ROW_NUMBER() OVER() PARTITION BY的使用
面朝大海，春暖花开
06-21 4071
SQL 去重
数据库 数据分组 后取最新一条
jpt2284012538的博客
11-24 1232
a.*,from(SELECTebip.id,FROMwhereGROUP BYo.id,ebip.idORDER BYLIMIT 5。
分组查询各组第一条记录
yitonglizihao的博客
07-12 2162
我们在查询数据的时候经常会遇到表中包含类似“类型”、“分组”等属性字段。然后需要查询出该分组下的第一条记录。 实现的SQL如下： select Name,Link,Type from ( SELECT ROW_NUMBER() OVER(partition by Type ORDER BY Name) as row, * FROM MovieInfo   WHERE Movi
mysql 根据日期和人分组获取每天每个人最后一条消息
最新发布
07-10
<think>我们有一个需求：根据日期和人分组，获取每天每个人最后一条消息。 假设我们有一个消息表（例如：messages），包含以下字段： - id: 消息的唯一标识 - person_id: 发送消息的人 - content: 消息内容 - created_at: 消息发送的时间（包括日期和时间） 我们需要按日期（即created_at的日期部分）和person_id分组，然后获取每个分组内最后一条消息（即created_at最大的那条消息）。 方法一：使用子查询 我们可以先按日期（使用DATE(created_at)）和person_id分组，然后找到每个分组内最大的created_at（即最后时间）。然后通过这个结果去关联原表，获取对应的整条消息。 但是注意：同一个人的同一天可能有多条消息，我们只需要每个分组（每天每人）中最后的那一条。 方法二：使用窗口函数（ROW_NUMBER） 在MySQL 8.0及以上版本，我们可以使用窗口函数ROW_NUMBER()来为每个分组内的记录按时间倒序排列，然后取每个分组的第一条。 这里我们提供两种方法的SQL语句。 方法一（适用于所有MySQL版本）： 步骤： 1. 首先，我们按日期（DATE(created_at)）和person_id分组，并找出每个组内最大的created_at（即最后一条消息的时间）。 2. 然后，将原表与这个结果进行连接，条件是相同的person_id、相同的日期以及相同的时间（即created_at相等）。 但是注意：有可能同一个人的同一天有两条消息在同一秒（甚至同一毫秒）发送，那么它们会有相同的created_at。这样可能会返回多条记录。但通常，我们假设created_at可以精确到足够区分每条消息（比如使用时间戳或包含微秒的datetime）。如果确实存在重复，那么可能需要其他条件（如最大id）来确保唯一性，但题目要求是最后一条消息，我们按最大时间取，如果时间相同，可以再取id最大的。 然而，题目要求是最后一条消息，我们按时间最大来取，如果时间相同，则任意取一条（或者按id最大取）？题目没有明确，我们先按时间最大取，如果时间相同，则取id最大的（即最新插入的那条）。 因此，我们可以先获取每个分组（每天每人）的最大时间和最大id（如果时间相同，取id最大的），但这样需要两个字段。另一种做法是先按时间和id排序，然后取每个分组的第一条。 方法一改进（使用两个子查询）： 先获取每个分组（每天每人）的最大时间，然后在这个时间下再取最大id（如果时间相同，取id最大的）。但这样需要两次分组。 但是，更高效的做法是使用一个子查询得到每个分组（每天每人）的最大时间，然后通过这个时间和person_id以及日期来关联原表，同时再关联一个条件：如果同一个分组有多个相同时间的记录，我们取id最大的。所以我们可以这样： 子查询1：按日期和person_id分组，得到最大时间。 然后原表与子查询1连接，条件是原表.person_id=子查询1.person_id and DATE(原表.created_at)=子查询1.date and 原表.created_at=子查询1.max_created_at 然后，再按日期、person_id和这个时间分组，取id最大的（因为同一个时间可能有多条，我们取id最大的）。 但这样可能比较繁琐。 方法一替代：使用相关子查询（但可能效率不高） 方法二（窗口函数，MySQL 8.0+）： 使用ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 然后取序号为1的。 这里我们给出两种方法的SQL。 方法一（使用两个子查询）： 步骤： 1. 先按日期（DATE(created_at)）和person_id分组，得到每个组的最大时间：max_created_at。 2. 再按日期（DATE(created_at)）、person_id和max_created_at分组，得到最大id（因为同一个人的同一天同一个时间可能有多条消息，我们取id最大的那条）。 3. 然后根据这些条件去原表查询。 或者，我们可以将第一步和第二步合并为一个子查询，即先按日期和person_id分组，然后取每个分组内created_at最大且id最大的记录？但是分组一次只能取一个最大值，不能同时取两个字段的最大值（除非使用group_concat等，但这样复杂）。 另一种思路：使用自连接和左连接排除 这里我们选择使用窗口函数（如果MySQL版本支持）会更简单。 方法二（窗口函数）的SQL： ```sql SELECT id, person_id, content, created_at FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(created_at), person_id ORDER BY created_at DESC, id DESC) AS rn FROM messages ) AS ranked WHERE rn = 1; ``` 解释： 在子查询中，我们按日期（DATE(created_at)）和person_id进行分组（分区），在每个分区内，我们按照created_at降序排列（这样最新的排在最前面），如果created_at相同，则按id降序排列（这样取id最大的）。然后我们取每个分区的第一行（rn=1）。 方法一（使用自连接和子查询）的SQL： 我们可以先获取每个分组（每天每人）的最大时间，然后获取在这个时间下最大的id（如果同一个时间有多条记录）。然后通过这两个字段去原表查询。 但是这样需要两个子查询，而且如果索引不合适，效率可能不高。 另一种方法：使用内连接，连接条件是同一个人的同一天，且原表的created_at等于最大时间，然后在这个基础上再取id最大的。我们可以这样： ```sql SELECT m1.* FROM messages m1 INNER JOIN ( SELECT DATE(created_at) AS date, person_id, MAX(created_at) AS max_created_at FROM messages GROUP BY DATE(created_at), person_id ) AS latest ON DATE(m1.created_at) = latest.date AND m1.person_id = latest.person_id AND m1.created_at = latest.max_created_at ``` 但是，这个查询可能返回多行（如果同一个人的同一天有多个消息在同一时间）。所以我们需要进一步去重。我们可以再嵌套一层，按日期、person_id和最大时间分组，然后取id最大的： ```sql SELECT m1.* FROM messages m1 INNER JOIN ( SELECT DATE(created_at) AS date, person_id, MAX(created_at) AS max_created_at, MAX(id) AS max_id -- 如果同一个时间有多条，我们取id最大的 FROM messages GROUP BY DATE(created_at), person_id ) AS latest ON DATE(m1.created_at) = latest.date AND m1.person_id = latest.person_id AND m1.created_at = latest.max_created_at AND m1.id = latest.max_id ``` 但是，这里有一个问题：在分组时，我们取的最大id可能不是最大时间对应的那条消息？因为分组后，我们同时取了最大时间和最大id，但是这两个最大值可能来自不同的记录。所以这样是不安全的。 因此，我们需要确保最大时间和最大id来自同一条记录。所以不能这样分组。 修正：先按日期和person_id分组得到最大时间，然后在这个分组的基础上，再按日期、person_id和最大时间去原表取这个时间点下最大的id。这样就需要两个子查询： ```sql SELECT m1.* FROM messages m1 INNER JOIN ( -- 先得到每个分组（每天每人）的最大时间 SELECT DATE(created_at) AS date, person_id, MAX(created_at) AS max_created_at FROM messages GROUP BY DATE(created_at), person_id ) AS latest_time ON DATE(m1.created_at) = latest_time.date AND m1.person_id = latest_time.person_id AND m1.created_at = latest_time.max_created_at -- 然后，再在同一个时间点内，取id最大的（因为同一个时间点可能有多条） INNER JOIN ( SELECT created_at, person_id, MAX(id) AS max_id FROM messages GROUP BY DATE(created_at), person_id, created_at -- 注意，这里我们按精确的created_at分组 ) AS latest_id ON m1.created_at = latest_id.created_at AND m1.person_id = latest_id.person_id AND m1.id = latest_id.max_id ``` 但是这样写很复杂，而且效率可能不高。 因此，我们更推荐使用窗口函数的方法（如果MySQL版本是8.0+）。 如果MySQL版本较低（比如5.7），我们可以使用以下方法： 使用相关子查询来获取每个分组（每天每人）的最后一条消息（按时间倒序，如果时间相同按id倒序，取第一个）： ```sql SELECT * FROM messages m WHERE m.id = ( SELECT id FROM messages WHERE DATE(created_at) = DATE(m.created_at) AND person_id = m.person_id ORDER BY created_at DESC, id DESC LIMIT 1 ) ``` 但是，这个查询会为每一行都执行一次子查询，效率很低，尤其是数据量大时。 另一种方法：使用LEFT JOIN来排除那些不是最后一条的记录。 ```sql SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON DATE(m1.created_at) = DATE(m2.created_at) AND m1.person_id = m2.person_id AND (m1.created_at < m2.created_at OR (m1.created_at = m2.created_at AND m1.id < m2.id)) WHERE m2.id IS NULL ``` 解释： 我们自连接，条件是同一个日期和同一个person_id，然后m1.created_at小于m2.created_at（说明m2比m1晚），或者时间相同但是m2的id更大（说明m2是更晚插入的）。那么，如果存在这样的m2，就说明m1不是这个分组中最后的一条。所以我们只保留那些没有找到m2的记录（即m2.id IS NULL），这些记录就是每个分组中created_at最大且id最大的。 这种方法在数据量大的时候可能效率也不高，但比相关子查询好一些。 总结：根据MySQL版本选择合适的方法。 如果MySQL版本>=8.0，推荐使用窗口函数。 如果MySQL版本<8.0，可以使用自连接的方法（但要注意性能）。 因此，我们给出两种方法的SQL： 方法1（窗口函数，MySQL>=8.0）： ```sql SELECT id, person_id, content, created_at FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY DATE(created_at), person_id ORDER BY created_at DESC, id DESC ) AS rn FROM messages ) AS ranked WHERE rn = 1; ``` 方法2（自连接，适用于所有版本，但需注意性能）： ```sql SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON DATE(m1.created_at) = DATE(m2.created_at) AND m1.person_id = m2.person_id AND ( m1.created_at < m2.created_at OR (m1.created_at = m2.created_at AND m1.id < m2.id) ) WHERE m2.id IS NULL; ``` 注意：自连接方法中，我们使用了严格的比较，确保每个分组只有一条记录被选出。 根据实际情况选择合适的方法。 另外，为了提高查询效率，建议在`created_at`和`person_id`上建立索引。 引用说明：我们在回答中使用了数据库查询的一般方法，没有直接引用外部资料，但可以参考数据库查询优化的通用实践[^1]。 §§相关问题§§ 1. 如果消息表非常大，如何优化这个查询？ 2. 在MySQL 5.7中，除了自连接还有哪些方法可以获取分组后的最大/最小值对应的行？ 3. 窗口函数ROW_NUMBER()和RANK()、DENSE_RANK()在分组查询中有什么区别？</think>### MySQL查询：按日期和人分组获取每天每人最后一条消息 假设有一个消息表 `messages`，包含以下字段： - `id`：消息ID（主键） - `person_id`：用户ID - `content`：消息内容 - `created_at`：消息时间戳（包含日期和时间） 以下是两种实现方法： #### ✅ 方法1：使用ROW_NUMBER()窗口函数（MySQL 8.0+推荐） ```sql SELECT id, person_id, content, created_at FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY DATE(created_at), person_id ORDER BY created_at DESC ) AS rn FROM messages ) AS ranked WHERE rn = 1; ``` **说明**： 1. `DATE(created_at)` 提取日期部分 2. `PARTITION BY DATE(created_at), person_id` 按日期和人分组 3. `ORDER BY created_at DESC` 按时间倒序排列 4. `rn = 1` 取每组第一条（即最新消息） #### ✅ 方法2：使用子查询（兼容MySQL 5.7及以下） ```sql SELECT m1.* FROM messages m1 INNER JOIN ( SELECT DATE(created_at) AS msg_date, person_id, MAX(created_at) AS latest_time FROM messages GROUP BY DATE(created_at), person_id ) AS latest ON DATE(m1.created_at) = latest.msg_date AND m1.person_id = latest.person_id AND m1.created_at = latest.latest_time; ``` **说明**： 1. 子查询获取每天每人的最新时间戳 2. 通过内连接匹配完整消息记录 3. 使用`DATE(created_at)`确保日期匹配 ### 性能优化建议 1. **索引优化**： ```sql CREATE INDEX idx_time_person ON messages (DATE(created_at), person_id, created_at); ``` 2. **分区表**：按日期范围分区可加速查询 3. **避免全表扫描**：确保`created_at`和`person_id`有索引 > 注意：如果同秒内有多条消息，可添加`id DESC`排序确保结果唯一性（如`ORDER BY created_at DESC, id DESC`）[^1]。 --- ###
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值