精华内容
下载资源
问答
  • mysql, 一对多查询, 统计一表数量

    千次阅读 2020-06-14 15:47:04
    简单的一对多, 关联会产生乘积, 如何统计, 这个太简单了, ....... SELECT COUNT( DISTINCT a.id ) FROM a LEFT JOIN b ON a.id = b.a_id COUNT 里面是本来就可以去重的, 对 a表去重

     

    简单的一对多, 关联会产生乘积, 如何统计, 这个太简单了, .......

    SELECT
    	COUNT( DISTINCT a.id ) 
    FROM
    	a
    	LEFT JOIN b ON a.id = b.a_id

    COUNT 里面是本来就可以去重的, 对 a表去重

    展开全文
  • 文章目录1 摘要2 情景复现2.1 数据模型2.2 核心代码2.3 测试数据2.4 拓展一点 ...一般情况下,不是查询结果条数不正确,就是一对多中的多的一方只能显示一条信息。本文将记录作者自己在项目中针对一对多分页查询主表...

    1 摘要

    分页查询是后台项目中最常见的一种操作,在一对多(one to many)的关系表中,如何根据主表进行分页是一个较为棘手的问题,尤其是查询条件既包含主表又包含从表/关联表的。一般情况下,不是查询结果条数不正确,就是一对多中的多的一方只能显示一条信息。本文将记录作者自己在项目中针对一对多分页查询主表和关联表都有查询条件的一种解决方案。

    关于分页查询,也可参考作者之前的笔记:

    mysql多表联合查询分页查询结果条数错误问题

    2 情景复现

    2.1 数据模型

    在先前的分页查询笔记中使用的是用户-用户图像这一简单的一对多模型,在本次示例中使用另一种更加复杂一些的模型,多对多模型: 文章与标签模型。

    一篇文章可以对应多个标签,同时一个标签也可以对应多篇文章。不过这里依旧以文章为主表。

    文章表:

    CREATE TABLE `article` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章 id,主键',
      `title` varchar(100) NOT NULL DEFAULT '' COMMENT '文章标题',
      `content` text  NOT NULL COMMENT '文章内容',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文章表';
    

    标签表:

    CREATE TABLE `article_tag` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章标签 id,主键',
      `tag_name` varchar(20) NOT NULL DEFAULT '' COMMENT '标签名称',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文章标签表';
    
    

    在多对多关系中,建议使用中间表进行关联

    文章与标签的关联表:

    CREATE TABLE `article_to_tag` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章-标签中间表',
      `article_id` bigint(20) NOT NULL COMMENT '文章 id',
      `tag_id` bigint(20) NOT NULL COMMENT '标签 id',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文章-标签关联表';
    

    2.2 核心代码

    由于在前边的笔记中已经介绍过一对多分页查询的方式: 使用子查询,因此这里就直接进入正题,不再演示不合理或者说错误的查询方法。

    需求: 根据条件查询文章,该条件既包含主表的字段,也(可能)包含子表的字段,要求使用分页查询

    统计查询结果总条数:

    SELECT COUNT(DISTINCT(a.id))
    FROM `article` a 
    LEFT JOIN `article_to_tag` att ON att.article_id = a.id 
    LEFT JOIN `article_tag` at ON at.id = att.tag_id
    WHERE a.id > 2
        AND at.tag_name LIKE CONCAT(CONCAT('%', '级'),'%')
    
    

    分页查询结果:

    SELECT 
      a.id, a.title, a.content, at.tag_name
    FROM (
        SELECT item_a.*
    		FROM `article` item_a
    		LEFT JOIN `article_to_tag` item_att ON item_att.article_id = item_a.id 
    		LEFT JOIN `article_tag` item_at ON item_at.id = item_att.tag_id
    		WHERE item_a.id > 2
            AND item_at.tag_name LIKE CONCAT(CONCAT('%', '级'),'%')
    		GROUP BY item_a.id
    		ORDER BY item_a.id DESC
    		LIMIT 0,5
    ) a 
    LEFT JOIN `article_to_tag` att ON att.article_id = a.id 
    LEFT JOIN `article_tag` at ON at.id = att.tag_id
    GROUP BY a.id, at.id
    ORDER BY a.id DESC
    
    

    这里演示的SQL中查询条件为: 文章的 id > 2,同时文章的标题中包含 「级」

    注意事项: 这里的子查询只是将查询总结果按照需要的方式排列,但是实际返回到前端的数据依然需要进行排序和分组,否则,依然会出现查询结果不符合要求的情况。即内层子查询和外层查询都需要进行排序和分组

    统计条数的查询结果为:

    4
    

    分页查询结果为:

    分页查询结果
    从结果总可以看出 id 为 5,6,7,8 的文章满足以上要求

    以上便是分页查询一对多主表子表都有查询条件的解决方案

    2.3 测试数据

    本示例中演示的测试数据,感兴趣的可以自行实践

    文章标签数据:

    -- 批量插入文章标签
    INSERT INTO `article_tag`(`tag_name`) VALUES
        ('初级'),
    		('中级'),
    		('高级'),
    		('超高级'),
    		('spring'),
    		('springBoot'),
    		('springMVC');
    

    文章表数据:

    -- 批量插入文章
    INSERT INTO `article` (`title`, `content`) VALUES
        ('好风光', '今天天气好晴朗,处处好风光'),
    		('冰雨', '冷冷的冰雨在我脸上胡乱地拍,你就像一个刽子手把我出卖'),
    		('学习', '好好学习,天天向上'),
    		('静夜思', '窗前明月光,疑是地上霜。举头望明月,低头思故乡。'),
    		('冬日里的一把火', '你就像那一把火,熊熊火焰燃烧了我'),
    		('演员', '简单点,说话的方式简单点。递进的情绪请省略,你又不是个演员'),
    		('小苹果', '你是我的小丫小苹果,怎么爱你都不嫌多'),
    		('雨一直下', '雨一直下,气氛不算融洽');
    

    文章与标签关联数据:

    -- 批量给文章添加标签
    INSERT INTO `article_to_tag` (`article_id`, `tag_id`) VALUES
        (1,1),
        (1,2),
        (1,4),
        (2,3),
        (2,5),
        (3,6),
        (4,7),
        (5,1),
        (5,2),
        (5,3),
        (6,4),
        (6,1),
        (6,5),
        (6,7),
        (7,6),
        (7,1),
        (8,3),
        (8,6),
        (8,7),
        (8,4);
    

    2.4 拓展一点

    文章通常有用户进行收藏,而在查询文章时,也可以显示文章的收藏量数据(这个需求是正常需求)

    文章的收藏量不同于文章的阅读量,文章的阅读量可以用一个字段就能够实现,有用户点增加值即可,但是用户收藏就不一样,因为用户可以查询到自己收藏的内容,这是一个正常的需求,反过来,某一篇文章被哪些用户收藏,这一功能也是可以实现的(不过这个需求个人感觉有些过分,有泄露隐私的嫌疑)

    OK,既然要实现用户收藏文章这一功能,就需要专门创建一个用户文章收藏表。

    用户文章收藏表:

    CREATE TABLE `article_user_favorite` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户收藏表id',
      `user_id` bigint(20) NOT NULL COMMENT '用户 id',
      `article_id` bigint(20) NOT NULL COMMENT '文章 id',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户文章收藏表';
    

    插入一些测试数据:

    -- 批量收藏文章
    INSERT INTO `article_user_favorite`(`user_id`, `article_id`) VALUES
        (1,1),
    		(1,2),
    		(1,3),
    		(1,4),
    		(1,5),
    		(1,6),
    		(1,7),
    		(1,8),
    		(2,1),
    		(3,1),
    		(4,1),
    		(2,2),
    		(2,6),
    		(3,1),
    		(4,1),
    		(5,1),
    		(3,2),
    		(3,6),
    		(3,7),
    		(4,8),
    		(4,5),
    		(5,2),
    		(5,3),
    		(5,4),
    		(5,5),
    		(5,6),
    		(5,7),
    		(5,8),
    		(4,4),
    		(3,3);
    
    

    还是按照上边的查询条件: 文章 id > 2, 文章的标签包含「级」
    这里需要显示每一篇文章的收藏量

    查询SQL:

    SELECT 
      a.id, a.title, a.content, at.tag_name,
    	COUNT(auf.id) AS countUserFavorite
    FROM (
        SELECT item_a.*
    		FROM `article` item_a
    		LEFT JOIN `article_to_tag` item_att ON item_att.article_id = item_a.id 
    		LEFT JOIN `article_tag` item_at ON item_at.id = item_att.tag_id
    		WHERE item_a.id > 2
            AND item_at.tag_name LIKE CONCAT(CONCAT('%', '级'),'%')
    		GROUP BY item_a.id
    		ORDER BY item_a.id DESC
    		LIMIT 0,5
    ) a 
    LEFT JOIN `article_to_tag` att ON att.article_id = a.id 
    LEFT JOIN `article_tag` at ON at.id = att.tag_id
    LEFT JOIN `article_user_favorite` auf ON auf.article_id = a.id
    GROUP BY a.id, at.id
    ORDER BY a.id DESC
    

    查询结果:

    查询结果-2

    个人公众号:404Code,记录半个互联网人的技术与思考,感兴趣的可以关注.
    404Code

    展开全文
  • 如果三表(包括了关系表)级联查询,主表和明细表的主键都是id的话,明细表的多条数据只能查询出来第一条/最后一条数据。 三个表,权限表(Permission),权限组表(PermissionGroup),权限组与权限的关系表...

    问题描述:
    如果三表(包括了关系表)级联查询,主表和明细表的主键都是id的话,明细表的多条数据只能查询出来第一条/最后一条数据。

    三个表,权限表(Permission),权限组表(PermissionGroup),权限组与权限的关系表(PermissionPermissionGroupKey)

    实体类就不写上来了。

    原出错映射文件:

      <resultMap id="permissionGroupResultMap" type="cn.kx59.admin.entity.PermissionGroup" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="decipher" property="decipher" jdbcType="VARCHAR" />
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        <!-- 一个权限组 对应多个权限-->
        <collection property="permissionArrayList" ofType="cn.kx59.admin.entity.Permission">
          <id column="id" property="id" jdbcType="INTEGER" />
          <result column="url" property="url" jdbcType="VARCHAR" />
          <result column="name" property="name" jdbcType="VARCHAR" />
          <result column="explain" property="explain" jdbcType="VARCHAR" />
          <result column="decipher" property="decipher" jdbcType="VARCHAR" />
          <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        </collection>
      </resultMap>
    
      <!--查询所有的权限组以及权限组包含的权限-->
      <select id="selectAllPermissionGroupAndPermission" resultMap="permissionGroupResultMap" >
            SELECT *
      FROM [QPWebDB].[dbo].[permission_group] pg
      left join [QPWebDB].[dbo].[permission_permission_group] ppg on pg.id=ppg.permission_group_id
      left join [QPWebDB].[dbo].[permission] p on p.permission_id=ppg.permission_id
      </select>

    这是因为主表和明细表的id字段名相同造成的。
    问题的关键在于resultMap中如果不定义类似主键之类的能够区分每一条结果集的字段的话,会引起后面一条数据覆盖前面一条数据的现象。

    解决方法一:
    修改主表或者明细表的id名,保证不一致就行

    解决方法二:
    查询结果起别名
    修改映射文件如下:

      <resultMap id="permissionGroupResultMap" type="cn.kx59.admin.entity.PermissionGroup" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="decipher" property="decipher" jdbcType="VARCHAR" />
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        <!-- 一个权限组 对应多个权限-->
        <collection property="permissionArrayList" ofType="cn.kx59.admin.entity.Permission">
          <id column="p_id" property="id" jdbcType="INTEGER" />
          <result column="url" property="url" jdbcType="VARCHAR" />
          <result column="name" property="name" jdbcType="VARCHAR" />
          <result column="explain" property="explain" jdbcType="VARCHAR" />
          <result column="decipher" property="decipher" jdbcType="VARCHAR" />
          <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        </collection>
      </resultMap>
    
      <!--查询所有的权限组以及权限组包含的权限-->
      <select id="selectAllPermissionGroupAndPermission" resultMap="permissionGroupResultMap" >
            SELECT pg.*,
      p.id p_id,
      p.url,
      p.name,
      p.explain,
      p.decipher,
      p.create_time
      FROM [QPWebDB].[dbo].[permission_group] pg
      left join [QPWebDB].[dbo].[permission_permission_group] ppg on pg.id=ppg.permission_group_id
      left join [QPWebDB].[dbo].[permission] p on p.id=ppg.permission_id
      </select>

    如上两种方法均能解决这个问题,希望对你有帮助
    有知道出现这个问题的原理的大佬欢迎在评论区解释下,O(∩_∩)O谢谢

    本文章由[谙忆]编写, 所有权利保留。
    欢迎转载,分享是进步的源泉。

    转载请注明出处:http://chenhaoxiang.cn

    本文源自人生之旅_谙忆的博客

    展开全文
  • MySQL SELECT COUNT 一对多关联查询去重

    千次阅读 2019-09-01 10:40:31
    一对多 关系时,统计一 的一方数量,这时使用 SELECT COUNT 就容易出现统计数目不准,有重复的现象。 2 问题复现 举例: 有一用户表(user) 和一张 用户图像表(user_photo),用户表与用户图像表属于一...

    1 摘要

    在实际项目中,通常会遇到多表关联查询的问题,这个时候在做分页查询的时候,可能会出现因关联关系而导致查询数量不正确的问题。如一对多 关系时,统计 的一方数量,这时使用 SELECT COUNT 就容易出现统计数目不准,有重复的现象。

    2 问题复现

    举例:
    有一用户表(user) 和一张 用户图像表(user_photo),用户表与用户图像表属于一对多关系,即一个用户可以拥有多张图像
    用户表:

    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id 主键',
      `user_name` varchar(30) DEFAULT NULL COMMENT '用户名',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
    
    

    用户图像表:

    DROP TABLE IF EXISTS `user_photo`;
    CREATE TABLE `user_photo` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '用户相册 id',
    `user_id` bigint NOT NULL DEFAULT 0 COMMENT '用户 id',
    `photo_url` varchar(255) NULL DEFAULT '' COMMENT '用户图片链接地址',
    PRIMARY KEY (`id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户图像表';
    
    

    要求: 使用分页查询用户信息,用户信息中需要包含用户的图像

    查询语句:
    统计用户数量:

    SELECT COUNT(u.id)
    FROM `user` u 
    LEFT JOIN `user_photo` up ON up.user_id = u.id
    

    查询结果:

    16
    

    实际用户数量为:

    SELECT COUNT(*) 
    FROM `user`;
    
    

    结果为: 11

    这时就出现了查询数量不准确的问题,因为是属于一对多的关系,所以,如果一个用户有两张图像的话,查询就会出现两条结果,从而导致查询数量不准确

    3 解决办法

    使用 DISTINCT 去除重复,保证每一个用户具有唯一性

    具体查询:

    SELECT COUNT(DISTINCT(u.id))
    FROM `user` u 
    LEFT JOIN `user_photo` up ON up.user_id = u.id
    

    查询结果为: 11
    符合实际用户数量

    个人公众号:404Code,记录半个互联网人的技术与思考,感兴趣的可以关注.
    404Code

    展开全文
  • django 聚合查询 统计Count 200316

    千次阅读 2020-03-17 17:40:03
    统计 统计 显示去重的结果 表内容 统计 分组统计 以图书为本,标记它的销量
  • 然后基于(并非完全遵守)关系型数据库的理论思想和SQL标准,很商业版或社区开源版的关系型数据库软件产品出现了。其中商业版比较知名的有IBM的DB2、Oracle的Oracle以及Microsoft的SQL Server;开源免费版比较流行...
  • es聚合查询维度数据统计

    万次阅读 多人点赞 2019-07-28 10:16:06
    根据业务需求,文档中的某个或某几个字段进行数据的分组并做一些指标数据的统计分析,比如要计算批文档中某个业务字段的总数,平均,最大最小值等,都属于聚合的范畴 以上两个概念后是理解下...
  • 种、通过count和if判断来统计 select COUNT(*) AS total, COUNT(if( mdt_apply_type='1' ,1,NULL) )as singleDiseasesNum, COUNT(if( mdt_apply_type='2' ,1,NULL) )as multidisciplinaryNum from t_mdt_apply_...
  • 今天遇到个问题,就是要统计一个数据库表中的相同字段的条数的问题。 SELECT conferenceId, COUNT(*) as num FROM meet_conference_attendee where conferenceId in (100740609,101650330,104095246) GROUP BY ...
  •  //创建用户且置密码,在MySQL... ----必须在超级管理员身份下操作 create user hncu identified by '1234' ...//查询(显示数据库) SHOW DATABASES; /*用Tab键补全代码,类似MyEclipse中的Alt+/ */ //创建
  • Solr Facet 统计查询

    千次阅读 2017-09-21 14:01:07
    Solr Facet查询 转载请出自出处:http://eksliang.iteye.com/admin/blogs/2165882 eksliang.iteye.com )概述  Facet是solr的高级搜索功能之,可以给用户提供更友好的搜索体验.在搜索关键字的同时,能够按照Facet...
  • 次、两次、三次是同个字段的不同值下的记录条数查询一列数据可以使用下面的SQL语句。select COUNT(*) from shuili_company a where a.check_number_ = 1 and a.is_delete = 0 from shuili_company ...
  • 需求如下:评论和回复是一对多的关系,并且根据评论进行分页查询,至于回复数量不做限制,有多少就查多少。 存在问题:由于两张表是用连接查询,所以对于同一评论存在多回复的情况,那么用sql查询出来的数量...
  • 昨天下午,公司的项目需要新增试卷的分析,就是根据一张试卷中的所有试题进行知识点统计,每个知识点各有几道题. 乍看十分简单,COUNT(0),GROUP BY就好了呀,可是问题就出在这个GROUP BY上,因为一道题可能对应...
  • tp5去重查询统计

    千次阅读 2019-04-09 15:50:44
    代码如下: 统计: $list['num']=db('web_allocation')//表名 ->where($where_n)//查询条件 ->count('distinct(package_type)');//要统计的字段 查询: ...
  • 我现在 有主表 A 和从表B  A表字段有: XM nvarchar2(100), RY_ID nvarchar2(32) B表字段有: RY_ID ...B的RY_ID为外键关联A表的RY_ID,所以A/B表形成了一对多的关系。 想查询的结果集包括A表中的记录,同时在B
  • 场景:数据库中id、toapp、topin、toclienttype几个字段都相同,receivetime字段不一样,现需要将receive最小的行查出,其他行舍去。 select * from ( select *, row_number() over(partition by id ...
  • solrj 开发solr查询统计方法

    千次阅读 2018-03-08 20:39:16
    java、solrj API开发solr查询统计方法solr常用的查询统计,通过solrj API实现, 有一般的条件过滤查询、分组查询、聚合查询
  • oracle-分组统计查询和子查询

    千次阅读 2019-05-21 16:15:42
    范例:统计所有雇员的人数,支付的总工资,平均工资,最高工资,最低工资 select count(empno),sum(sal),avg(sal),max(sal),min(sal) from emp; 范例:统计带工薪的总工资和平均工资 ...
  • 如果查询30天的数据, 会for 循环30天,然后再天的数据去查询拼成数组。 这样统计时候会使性能降低。 sql 优化 用了mysql 的函数 date_format select count(1) as count from 查询的表 group by date_...
  • linux下如何统计一个目录下的文件个以及代码总行的命令 知道指定后缀名的文件总个命令:  find . -name "*.cpp" | wc -l  知道个目录下代码总行以及单个文件行数:  find . -name "*.h" | xargs ...
  • oracle count 百万级 查询记录总数、总条数优化 最近做个项目时,做分页时,发现分页查询速度很慢,分页我做的是两次查询次是查询总数,次是查询分页结果 Java代码 /** 查询总记录数 **/  SELECT
  • MySql 的统计查询性能问题

    千次阅读 2013-09-07 10:31:19
    由于数据量非常大(单表:四五百万数据),需要这种类型的表进行统计查询,并插入对应的Maxstatistics(里面是Id、Value、Time) MinStatistics 等表。 这个表的结构如下:CREATE TABLE `datarecord` ( `Id`
  • 业务场景为统计项目表和用户表关联查询,项目id为用户的关联字段,个项目下有个项目,目标是查出来项目id,产品编号(项目里的字段)和某个项目id下的用户总数,过滤调剂是过滤掉过期的数据和被禁用及被删除的...
  • SQL语言查询、分组统计、子查询、数据表的更新操作、事务处理3.1、查询3.1.1、基本语法但是在查询之前首先必须处理个问题:例如:现在求出雇员表中的总记录(14记录)SELECT COUNT(*) FROM emp ;...
  • 如果iPhone手机系统升级iOS8之后就会发现个健康-app,这就是Apple提供的个记录用户健康信息的app,可以用它来分享健康和健身数据。还可以指定数据的来源,比如我们自己创建个app,在我们的app中使用了...
  • Oracle统计多张表的Count的和

    万次阅读 2018-11-07 15:14:04
    将两张表融合起来,group by一下再每个分组分别sum一下就可以计算出各自status的总数,但会遇到个问题,new怎么addprocess中? > 用oracle的decode函数,即在查上面的count的时候,就把new的value给改成...
  • 高性能MySQL之Count统计查询

    万次阅读 2018-07-24 12:02:59
    当时在忙,就回复了句“innodb里面count统计都是实时统计,慢一些是正常的”, 周末闲暇下来,想到以前有好多人都问过关于count的问题,今天就聊聊MySQL之Count查询。  关于MySQL的count查询,很多人都会有疑问...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 160,574
精华内容 64,229
关键字:

一对多统计查询到的条数