精华内容
下载资源
问答
  • Mysql取分组后每组第一条数据 gruop by 分组后 进行 order by mysql会按照 先分组后排序的形式进行输出 并不能做到每组中的第一数据取出。 我的思路是 : 先将要查询的数据表转换成已经排序的临时表 在进行 分组...
  • SQL查询之分组后取每组的前N记录

    万次阅读 多人点赞 2019-03-02 23:51:05
    SQL查询之分组后取每组的前N记录 、背景 资讯信息实体 code 说明 id 主键 title 资讯名称 views 浏览量 info_type 资讯类别 资讯分类实体 id ...

    SQL分组查询后取每组的前N条记录

    一、前言

    分组查询是常见的SQL查询语句。首先,我们知道MySQL数据库分组功能主要是通过GROUP BY关键字来实现的,而且GROUP BY通常得配合聚合函数来使用用,
    比如说分组之后你可以计数(COUNT),求和(SUM),求平均数(AVG)等。但是今天我们要探讨的不是GROUP BY关键字学习和使用,而是一种有点另类的“分组”查询。

    最近,项目上遇到这样一个功能需求。系统中存在资讯信息这样一个功能模块,用于发布一些和业务相关的活动动态,其中每条资讯信息都有一个所属类型(如科技类的资讯、娱乐类、军事类···)和浏览量字段。
    而业务系统的官网上需要滚动展示一些热门资讯信息列表(浏览量越大代表越热门),而且每个类别的相关资讯记录至多显示3条,换句话:“按照资讯分类分组,取每组的前3条资讯信息列表”。后面在尝试GROUP BY
    使用的各种方式都不能实现,最后在查阅相关资料后找到了实现的解决方法。

    下面,我将模拟一些实际的测试数据重现问题的解决过程。

    一、数据准备

    1. 数据库

      MySQL 8.0社区版

    2. 表设计

      1. 资讯分类表
        id主键
        name分类名称
      2. 资讯信息记录表
        code说明
        id主键
        title资讯名称
        views浏览量
        info_type_id资讯类别
    3. 初始化SQL语句

      DROP TABLE IF EXISTS `info`;
      CREATE TABLE `info`  (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
        `views` int(255) DEFAULT NULL,
        `info_type_id` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
      
      -- ----------------------------
      -- Records of info
      -- ----------------------------
      INSERT INTO `info` VALUES (1, '中日海军演习', 10, 4);
      INSERT INTO `info` VALUES (2, '美俄军事竞赛', 22, 4);
      INSERT INTO `info` VALUES (3, '流浪地球电影大火', 188, 1);
      INSERT INTO `info` VALUES (4, '葛优瘫', 99, 2);
      INSERT INTO `info` VALUES (5, '周杰伦出轨了', 877, 2);
      INSERT INTO `info` VALUES (6, '蔡依林西安演唱会', 86, 1);
      INSERT INTO `info` VALUES (7, '中纪委调盐', 67, 3);
      INSERT INTO `info` VALUES (8, '人民大会堂', 109, 3);
      INSERT INTO `info` VALUES (9, '重庆称为网红城市', 202, 1);
      INSERT INTO `info` VALUES (10, '胡歌结婚了', 300, 2);
      INSERT INTO `info` VALUES (11, 'ipone15马上上市', 678, 2);
      INSERT INTO `info` VALUES (12, '中国探月成功', 54, 4);
      INSERT INTO `info` VALUES (13, '钓鱼岛对峙', 67, 4);
      
      DROP TABLE IF EXISTS `info_type`;
      CREATE TABLE `info_type`  (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
      
      -- ----------------------------
      -- Records of info_type
      -- ----------------------------
      INSERT INTO `info_type` VALUES (1, '娱乐');
      INSERT INTO `info_type` VALUES (2, '八卦');
      INSERT INTO `info_type` VALUES (3, '政治');
      INSERT INTO `info_type` VALUES (4, '军事');
      

      资讯分类示例数据如下:

      在这里插入图片描述

      资讯信息记录表示例数据如下:

      在这里插入图片描述

    4. 需求

      取热门的资讯信息列表且每个类别只取前3条。

    二、核心思想

    一般意义上我们在取前N条记录时候,都是根据某个业务字段进行降序排序,然后取前N条就能实现。形如“select * from info order by views asc limit 0,3 ”,这条SQL就是取info表中的前3条记录。但是当你仔细阅读我们的题目要求,你会发现:“它是让你每个类型下都要取浏览量的前3条记录”。一种比较简单但是粗暴的方式就是在Java代码中循环所有的资讯类型,取出每个类型的前3条记录,最后进行汇总。虽然这种方式也能实现我们的要求,但存在很严重的弊端,有可能会发送多次(夸张的说成百上千次也是有可能)sql语句,这种程序显然是有重大缺陷的。

    但是,我们换一种思路。我们想在查询每条资讯记录时要是能查出其所在类型的排名就好了,然后根据排名字段进行过滤就好了。这时候我们就想到了子查询,而且MySQL是可以实现这样的功能子查询的。要计算出某条资讯信息的在同资讯分类下所有记录中排第几名,换成算出 有多少条浏览量比当前记录的浏览量高,然后根据具体的多少(N)条+1就是N+1就是当前记录所在其分类下的的排名。

    假如以本文上面的示例数据说明:就是在计算每个资讯信息记录时,多计算出一列作为其“排名”字段,然后取“排名”字段的小于等于3的记录即可。如果这里还不是很理解的话,就先看下面的SQL,然后根据SQL再回过头来理解这段话。

    三、SQL实现

    1. 方法一

      1. SQL语句
        SELECT t.* from ( SELECT t1.*, (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views ) top FROM info t1 ) t where top <=3 order by t.info_type_id,top

      2. 查询结果

        在这里插入图片描述

      3. 说明
        分析top字段的子查询,发现其满足条件有两个:其一是info_type_id和当前记录的type_id相等;其二是info表所有记录大于
        当前记录的浏览量且info_type_id相等的记录数量(假设为N),所有N+1就等于当前记录在其分类下的按照浏览量降序排名。

    2. 方法二

      1. SQL语句

        	SELECT
        		t1.*	
        	FROM
        	info t1
        	where (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views ) <=3
        	ORDER BY t1.info_type_id
        
      2. 查询结果

        在这里插入图片描述

      3. 说明
        方法二可以看做是方法一的变体

    3. 方法三

      1. SQL语句
        	SELECT
        		t1.*	
        	FROM
        	info t1
        	where exists (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views having (count(*) + 1) <= 3) 
        	ORDER BY t1.info_type_id
        
      2. 查询结果
        在这里插入图片描述

    四、小结

    其实,有时候在面临业务难题的时候,困难的地方往往不在技术本身,而在于我们解决问题的思维方式。
    就正如案例中求记录的所在分类的排名,把其对等的“转换成有多少条同类别的记录的浏览量比当前记录的大(count聚合函数)”
    问题马上就迎刃而解了。

    展开全文
  • select * from ( select F_Id,F_EnCode,F_FullName,F_LotNum,ROW_NUMBER() OVER(PARTITION BY F_EnCode,F_FullName ORDER By F_CreatorTime Desc) AS Row_Sort,F_CreatorTime ...) A where A.Row_Sort=1...

    select * from
    (
    select F_Id,F_EnCode,F_FullName,F_LotNum,ROW_NUMBER() OVER(PARTITION BY F_EnCode,F_FullName ORDER By F_CreatorTime Desc) AS Row_Sort,F_CreatorTime
    from Mst_Test 
    ) A where A.Row_Sort=1

    PARTITION BY 后面接分组凭借字段(通过什么来分组)

    ORDER By 后面接排序字段 (根据什么排序)

    ROW_NUMBER() 每条数据的行号     OVER  行号根据什么得到

    效果如下:

    展开全文
  • oracle 分组后取每组第一条数据

    千次阅读 2017-05-25 15:20:10
    因为项目中需要将结果集中的相同分组中的数据在内排序然后再分别取出分组第一条记录,所以研究了下 ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) 主要思路就是根据不同分组创建一个字段进行内...

    因为项目中需要将结果集中的相同分组中的数据在组内排序然后再分别取出每个分组的第一条记录,所以研究了下 

    ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC)

    主要思路就是根据不同分组创建一个字段进行组内排序,PARTITION BY 是按照某字段分组,例子如下:

    数据格式

    分组取第一条的效果


    sql

     

     SELECT *       
        FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,       
              test1.*       
              FROM test1)       
       WHERE rn = 1  ;  

    展开全文
  • mysql分组取每组前几记录(排名) 附group by与order by的研究,需要的朋友可以参考下
  • SELECT M.* from (SELECT Model.*,ROW_NUMBER () OVER ( PARTITION BY id, pid ORDER BY CreateTime ) AS RN1, ROW_NUMBER () OVER ( PA...

    SELECT M.*  from
    (SELECT
    Model.*,ROW_NUMBER () OVER (
                PARTITION BY id,
                pid
            ORDER BY
                CreateTime
            ) AS RN1,
            ROW_NUMBER () OVER (
                PARTITION BY id,
                pid
            ORDER BY
                CreateTime DESC
            ) AS RN2
        FROM
            Model
    ) M
    WHERE    
    RN1=1 or RN2 = 1

     

    重点:ROW_NUMBER () OVER (
                PARTITION BY id,
                pid
            ORDER BY
                CreateTime
            ) AS RN1 

    //这个是按从小到大编号,取rn1=1z则取出每组的第一条

    ROW_NUMBER () OVER (
                PARTITION BY id,
                pid
            ORDER BY
                CreateTime DESC
            ) AS RN2

    //这个是按从大到小编号,取rn2 =1 取出的是实际我们要的最后一条,这样就满足我们要求了,效率还好.

    展开全文
  • 分组查询后取每组的前N记录 标签 mysql 数据库 分组 TOPN 、背景 最近,有个功能需求。系统有个发布资讯模块,要求按照卡片形式展示。如下图: 按照项目组展示卡片,每个项目组展示阅读量最多的TOP2...
  • 这里插句,MySQL高版本5.7以后,sql_mode有变化,其中之体现为,group by写法。可查看文章了解: 远程centOS6.8final,MySQL5.6; 表结构,数据都是一样; 测试SQL语句: SELECT SwipeID,MemberID,AddTime FROM...
  • SQL分组后取每组的前几行数据 现在有这么个场景,有个交易的记录表,记录所有商品的购买记录,现在求出每个商家销量最高的两个商品。 建表准备数据: CREATE TABLE detail( id bigint(20) AUTO_INCREMENT ...
  • 思路:先随机排序然后再分组就好了。 1、创建表: CREATE TABLE `xdx_test` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `class` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=...
  • 表 (5-8秒刷新次) 更新时间、采集点编号和最新读数 说明一下 表里面有很多点位数据 然后固定时间insert次 我需要拿到 最新时间的 所有点位的 数据 create table C010_CURRENT_DATA ( ID varchar(50) not ...
  • 在实际工作中,有时我们需要用SQL语句对数据分组后取出每一组内的某一条(如第1条,第2……第n,或者第1大/小,第2大/小……第n大/小)记录,下面将介绍实现这需求的最简单的SQL查询方式。 需求:查询门...
  • select * from(select row_number() over(partition by 【分组列】 order by【排序列】desc/asc) as rownum,* from (SELECT * FROM 【表名】 )as G1 ) as G where G.rownum =1
  • sql 分组取分组第一条数据汇总 关键sql PARTITION BY x,y (分组字段,相当于group by) ORDER BY z --排序字段 ROW_NUMBER() OVER(PARTITION BY dtc.terminal_code,dtc.dealer_id ORDER BY dtc.created_time DESC) ...
  • 使用SQL Server数据库在【分组排序并取出每组中的第一条数据】的场景下,很容易想到的是使用GROUP BY分组子句配合聚合函数。 举个简单的例子,有一个YANGGBS表,表中有NAME和AGE两个字段,要求统计出每个NAME的最大...
  • hive sql分组后取每组前100

    千次阅读 2020-05-12 18:51:18
    最近在进行风控系统-反黑策略的时候遇见个有趣的SQL select * from ( select *,RANK() OVER (PARTITION BY strategy_id ORDER BY result_id DESC ) as sort_id from distinct_uid_strategy_result ) t where...
  • 使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 先进行分组 注:根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序的顺序编号(组内连续的唯一的). sql语句为: select ...
  • mysql分组后取每组第一条数据

    千次阅读 2021-05-06 19:56:41
    环境 MySQL:5.7 Java:1.8 SQL语句的写法: select * from (select distinct(a.id) tid, a.* from template_detail a ...思路:先进行排序,然后再进行分组,获取每组第一条。 Q: 为什么要写distinct(a.i
  • oracle和mysql中根据多字段分组后取排序第一条数据,所取出的数据包括除分组字段外的其它字段。
  • SELECT * FROM (  SELECT *, ROW_NUMBER() OVER (PARTITION BY groupByField ORDER BY orderByField DESC) rn FROM t1 ) t WHERE rn = 1 转载于:https://www.cnblogs.com/laop/p/10400726.html
  • 原文:sql 分组后按时间降序排列再取出每组第一条记录 竞价记录表: Aid 为竞拍车辆ID,uid为参与竞价人员ID,BidTime为参与竞拍时间 查询出表中某人参与的所有车辆的最新的一的竞价...
  • 要实现这样的效果我有两思路,是按照币种分组后日期最大的所有数据。SQL如下: SELECT * FROM Exchangerate SS1 INNER JOIN ( SELECT max ( AddDate ) AS AddDate , MoneyType FROM ...
  • 开发中经常会遇到,分组查询最新数据的问题,比如下面这张表(查询个地址最新的一条记录): sql如下: 复制代码 – Table structure for test DROP TABLE IF EXISTS test; CREATE TABLE test ( id int(11) NOT ...
  • mysql分组取每组前几记录(排名) 附group by与order by的研究 http://www.jb51.net/article/31590.htm --按某字段分组取最大(小)值所在行的数据 代码如下: /* 数据如下: name val memo a 2 a2(a的...
  • Sql分组后取每的前几记录

    千次阅读 2017-04-12 16:23:50
    SELECT C_BH,C_BT,C_BirthCorpID FROM  (   SELECT rid=ROW_NUMBER()   OVER(PARTITION BY C_BirthCorpID   ORDER BY D_FBSJ DESC),   *   FROM T_G
  • 编辑 ...1 --建立测试环境 CREATE TABLE table1(a VARCHAR(10),b VARCHAR(10),c VARCHAR(10)); --插入数据 INSERT INTO table1 VALUES('2004','12','storea'); INSERT INTO table1 VALUES('200...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 78,070
精华内容 31,228
关键字:

sql分组后取每组第一条