`
denger
  • 浏览: 359212 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Mysql多对多查询、列合并

阅读更多
表信息
  • 资源表 synsource                                                 199824       rows
  • 产品表 tab_product_detail                                  108              rows
  • 资源与产品多对多的关联表 tab_r_sourmach  1,113,866   rows
以上表均采用MyISAM引擎.

连接测试
   因 为方便用户进行更好的资源的搜索,所以需要将资源数据全部建立索引至Lucene中,希望在Lucene中存储的Document为:
sourceNamefileNamesubjectgradeproducts
英语学生用书第十一册外研新标准.nwe英语小学NP7000 NP6000 NP2300
英语学生用书第十二册外研新标准2.nwe英语小学NP2300

以上前四个字段是属于 synsource表中,而 products 属于 tab_product_detail 表,它们之间的关系由tab_r_sourmach进行中间关联。
一开始想到的是直接使用以下SQL:
select s1.sourid, s1.sourcename , t.product_name from synsource s1 
  left join tab_r_sourmach c 
    on s1.sourid=c.sourid
      left join tab_product_detail t 
        on c.product_id = t.product_id where s1.sourceid=1

5 rows in set (0.45 sec)
sourid sourcename        subjectname product_name
      1 小六上Module07    英语        ND520       
      1 小六上Module07    英语        NP560T      
      1 小六上Module07    英语        NP560+      
      1 小六上Module07    英语        NP360+      

对于以上SQL的结果重复数据的合并处理考虑采用了 GROUP_CONCAT
select s1.*, GROUP_CONCAT(t.product_name SEPARATOR ' ') as product_name
  from tab_synsource s1 
    left join tab_r_sourmach c
      on s1.sourid=c.sourid
        left join tab_product_detail t 
              on c.product_id = t.product_id where s1.sourceid=1

1 row in set (0.31 sec)
sourid sourcename        subjectname product_name 
1 小六上Module07    英语        ND520 NTV518 NP560T NP560+ NP360+

看上去似乎能够满足需求,但是以上只是针对单条数据进行查询,对于数据的批量索引建立的话肯定是直接 limit 500之类的操作。于是去掉以上where加上 limit 10执行之后,半天都执行不出来,估计它先会全部数据连接查询之后再去 limit。
于是先测试一下直接(tab_r_sourmach, tab_product_detail)两表关联看看效率如何:
select c.sourid
from tab_r_sourmach c left join tab_product_detail t 
on  c.product_id = t.product_id  
group  by c.sourid limit 500;

以上语句用时:(5.65 sec)

再加上GROUP_CONCAT试试:
select c.sourid, GROUP_CONCAT(t.product_name SEPARATOR ' ') as products
from tab_r_sourmach c left join tab_product_detail t 
on  c.product_id = t.product_id  
group  by c.sourid limit 800;

输出结果样例:
sourid products
    767 ND520 ND416 NTV518 NP560T NP560+ NP360+
    760 ND550 NP600+ NP9588 NP600

800 rows in set (1.74 sec) , 经过反复测试都发现加上了GROUP_CONCAT 函数比没使用该函数快了五倍多,暂时还不知道为什么。
不过1秒左右对于后台索引建立来说还算能够接受,于是再将以上SQL与 synsource 进行连接测试:
select * from synsource s, (
  select c.sourid,
      GROUP_CONCAT(t.product_name SEPARATOR ' ') 
      from tab_r_sourmach c left join tab_product_detail t 
      on 
      c.product_id = t.product_id 
      group  by c.sourid  limit 1000
    ) as b
where s.sourid = b.sourid;

输出结果样例:
    968 词汇学习-第三级K(参考剑桥少儿英语)   英语        NP600+ NP600
    983 词汇学习-六年级上Unit7(参考湘教版)   英语        ND520 ND416 NTV518 NP560T NP560+ NP360+

1000 rows in set (2.02 sec)


不知道大家对这种情况如何处理的?以上的SQL虽然能够达到目的,不过还是存在不足。

忘说了,在使用MYSQL GROUP_CONCAT函数进行分组连接时,它对字符串的长度连接是有长度限止的,默认的情况的话我发布 products 太长的话会被截断,如果想一劳永逸的话直接在my.cnf 中加入 group_concat_max_len=99999 即可。
分享到:
评论
2 楼 denger 2011-04-15  
java_user 写道
问下,那数据说的是诺亚舟点读机?

1 楼 java_user 2011-04-15  
问下,那数据说的是诺亚舟点读机?

相关推荐

    mysql行转列(将同一列下的不同内容的几行数据,转换成几列显示)、列转行、行列汇总、合并显示

    MySQL 合并显示是将多个查询结果合并到一个查询结果的操作。例如,我们可以使用 UNION ALL 语句来将多个查询结果合并到一个查询结果。 SELECT userid, SUM(IF(`subject`='语文', score, 0)) AS 语文, SUM(IF...

    mysql查询根据列按条件统计总数

    ### MySQL 查询根据列按条件统计总数 在MySQL中,经常需要对数据进行统计分析,特别是针对某一特定条件下的数据总量的统计。此类操作通常涉及到`COUNT()`函数与`GROUP BY`语句的结合使用,有时还需要与其他子查询或...

    mysql多表查询和EXISTS查询性能对比

    LEFT JOIN 在处理复杂查询时非常有用,尤其是在需要合并多个表的信息时。然而,它的执行效率通常不如使用NOT EXISTS或NOT IN,特别是在右表记录较少的情况下。 #### 总结 从以上分析可以看出,每种查询方式都有其...

    mysql_一列分多行操作,在一个表里的几行数据需要分拆成单行存储到一行中

    mysql_一列分多行操作,在一个表里的几行数据需要分拆成单行存储到一行中,mysql_一列分多行操作,在一个表里的几行数据需要分拆成单行存储到一行中 mysql_一列分多行操作,在一个表里的几行数据需要分拆成单行存储...

    Mysql、oracle、Sybase数据库两列合并成一列的sql语句

    在数据库管理与开发过程中,经常会遇到需要将表中的两列数据合并为一列的情况。这种需求常见于数据清洗、报表生成或数据展示等场景。不同的数据库管理系统(Database Management System,DBMS)提供了各自的方法来...

    mysql复杂动态行转列解决方案

    在传统的SQL查询中,我们可以使用`CASE`语句配合`GROUP BY`来实现简单的行转列转换,但这种方法往往不适用于动态列,即列的数量和名称可能根据不同的数据而变化。在这种情况下,我们通常会依赖于`PIVOT`操作,但在...

    SQL合并一列

    这里我们将重点介绍第二种方法,即利用`FOR XML PATH`实现列合并的技术。 #### 2. 使用`FOR XML PATH`实现列合并 `FOR XML PATH`是一种非常强大的工具,可以用来构建XML格式的数据,同时也可以用来构建普通的字符...

    mysql数据库的多表设计和各种查询(连接查询\子查询\联合查询\报表查询),以及数据库备份和恢复

    3. 联合查询(UNION):联合查询用于合并两个或更多SELECT语句的结果集,但要求所有查询返回相同数量和类型的列。 4. 报表查询(Report Query):这类查询通常涉及聚合函数(如SUM, AVG, COUNT等)和GROUP BY语句,...

    mysql多表查询准备

    - ORDER BY用于对查询结果进行排序,可以指定升序(ASC)或降序(DESC)。 - LIMIT用于限制返回的记录数量,这对于分页查询非常有用。 通过掌握这些知识点,你可以有效地在MySQL中执行复杂的多表查询,解决实际...

    使用案例加深对MySQL SQL查询理解

    JOINs可以合并多个表的数据,但如果不正确使用,可能会导致性能下降。使用INNER JOIN、LEFT JOIN、RIGHT JOIN等不同类型的JOIN取决于你的需求,合理设计表结构和选择合适的JOIN类型能提高查询效率。 子查询也是SQL...

    25.3 MySQL 多表查询

    7. **联合查询(UNION)**:将两个或更多 SELECT 语句的结果合并成一个结果集。注意,所有 SELECT 语句必须有相同数量的列,且列的数据类型要兼容。 8. **笛卡尔积(CARTESIAN JOIN)**:如果没有指定任何连接条件...

    MySql基本查询、连接查询、子查询、正则表达查询讲解

    用户可以根据自己对数据的需求,使用不同的查询方式。通过不同的查询方式,可以获得不同的数据。MySQL中是使用SELECT语句来查询数据的。在这一章中将讲解的内容包括。 1、查询语句的基本语法 2、在单表上查询数据 3...

    MySQL多表查询

    这种查询通常用于获取特定的数据子集或对查询结果进行进一步过滤。 **子查询**: ```sql SELECT id, name, sex, date FROM tb_demo068 WHERE id IN (SELECT id FROM tb_demo068 WHERE id = '$_POST[test]'); ``` *...

    设计高效合理的MySQL查询语句

    总之,设计高效合理的MySQL查询语句涉及到索引策略、排序优化、顺序存取的避免以及查询语句的精简等多个方面。通过综合运用这些技巧,可以显著提升数据库应用的性能,满足大规模数据处理的需求。

    将所有模板数据表合并为一个大表

    2. 数据表合并:当有多个这样的模板数据表时,可能需要将它们合并成一个大的表,以便于数据查询、分析或减少重复的数据处理。合并方式通常有两种:水平合并(并行合并)和垂直合并(堆叠合并)。 - 水平合并:也...

    计算机病毒与防护:MYSQL联合查询上.ppt

    这种方法基于对查询结果是否影响页面输出的观察。例如,通过在SELECT语句末尾添加ORDER BY N,如果N超出实际列数,查询会失败,导致页面显示异常。攻击者通过逐渐增加N的值,可以确定结果集的列数。 6. **判断列...

    mysql查询gitbook书籍

    8. **ORDER BY子句**:用于对查询结果进行排序,可以按升序(ASC)或降序(DESC)排列。 9. **索引**:为了提高查询性能,可以在列上创建索引。B-Tree、Hash、Full-text和Spatial索引是常见的类型。 10. **视图**...

    MySql 5.1 参考手册.chm

    2.3.14. 在Windows环境下对MySQL安装的故障诊断与排除 2.3.15. 在Windows下升级MySQL 2.3.16. Windows版MySQL同Unix版MySQL对比 2.4. 在Linux下安装MySQL 2.5.在Mac OS X中安装MySQL 2.6. 在NetWare中安装MySQL 2.7....

    sql 多行合并某一列字符串拼接的示例

    4. **执行主查询**:最后,执行主查询,使用`GROUP BY`子句按合并条件对数据进行分组,确保每组数据只出现一次。 通过这种方式,我们可以有效地将多行数据转换为一行,同时在一个列中拼接所需的字符串值。这种方法...

    MySQL海量数据查询优化策略.

    16. 考虑聚集索引和非聚集索引:聚集索引决定了数据在磁盘上的物理排序,对查询性能有较大影响。在设计表结构时要根据查询模式和数据访问模式合理安排聚集索引。 17. 优化数据类型和字符集:合理选择数据类型和字符...

Global site tag (gtag.js) - Google Analytics