首页 / MYSQL / 重复键的Mysql group_concat和1个查询中多列重复的计数(查询优化)

重复键的Mysql group_concat和1个查询中多列重复的计数(查询优化)

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了重复键的Mysql group_concat和1个查询中多列重复的计数(查询优化)，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10520字，纯文字阅读大概需要16分钟。

内容图文

这个问题是关于查询优化,以避免通过PHP多次调用数据库.

所以这是场景,我有两个表,其中包含您可以将其称为参考表的信息,另一个是数据表,字段key1和key2在两个表中都是通用的,基于这些字段,我们可以加入它们.

我不知道查询是否可以比我现在正在做的更简单,我想要实现的如下：

I would like to find distinct key1,key2,info1,info2 from main_info
table, whenever serial value is less than 10 and key1,key2 of both
table matches, and then group them by info1,info2, while grouping
count the repeated key1,key2 for duplicates of info1,info2 fields
and group_concat those keys

表main_info的内容

MariaDB [demos]> select * from main_info;
+------+------+-------+-------+----------+
| key1 | key2 | info1 | info2 | date     |
+------+------+-------+-------+----------+
|    1 |    1 |    15 |    90 | 20120501 |
|    1 |    2 |    14 |    92 | 20120601 |
|    1 |    3 |    15 |    82 | 20120801 |
|    1 |    4 |    15 |    82 | 20120801 |
|    1 |    5 |    15 |    82 | 20120802 |
|    2 |    1 |    17 |    90 | 20130302 |
|    2 |    2 |    17 |    90 | 20130302 |
|    2 |    3 |    17 |    90 | 20130302 |
|    2 |    4 |    16 |    88 | 20130601 |
+------+------+-------+-------+----------+
9 rows in set (0.00 sec)

表product1的内容

MariaDB [demos]> select * from product1;
+------+------+--------+--------------+
| key1 | key2 | serial | product_data |
+------+------+--------+--------------+
|    1 |    1 |      0 | NaN          |
|    1 |    1 |      1 | NaN          |
|    1 |    1 |      2 | NaN          |
|    1 |    1 |      3 | NaN          |
|    1 |    2 |      0 | 12.556       |
|    1 |    2 |      1 | 13.335       |
|    1 |    3 |      1 | NaN          |
|    1 |    3 |      2 | 13.556       |
|    1 |    3 |      3 | 14.556       |
|    1 |    4 |      3 | NaN          |
|    1 |    5 |      3 | NaN          |
|    2 |    1 |      0 | 12.556       |
|    2 |    1 |      1 | 13.553       |
|    2 |    1 |      2 | NaN          |
|    2 |    2 |     12 | 129          |
|    2 |    3 |     22 | NaN          |
+------+------+--------+--------------+
16 rows in set (0.00 sec)

通过PHP我将表main_info的字段info1和info2分组,在表product1的当前上下文serial,product_data中,一个接一个地多次(这里我运行查询两次,你可以看到)

对于字段序列 – 第一个查询

MariaDB [demos]> select * , count(*) as serial_count,GROUP_CONCAT(key1,' ',key2) as serial_ids from 
    -> (
    -> SELECT distinct 
    -> if(b.serial  < 10,a.key1,null) AS `key1`,
    -> if(b.serial  < 10,a.key2,null) AS `key2`,
    -> if(b.serial  < 10,a.info1,null) AS `info1`, 
    ->         if(b.serial  < 10,a.info2,null) AS `info2`
    -> FROM main_info a inner join product1 b on  a.key1 = b.key1 AND a.key2= b.key2
    -> ) as sub group by info1,info2
    -> ;
+------+------+-------+-------+--------------+-------------+
| key1 | key2 | info1 | info2 | serial_count | serial_ids  |
+------+------+-------+-------+--------------+-------------+
| NULL | NULL |  NULL |  NULL |            1 | NULL        |
|    1 |    2 |    14 |    92 |            1 | 1 2         |
|    1 |    3 |    15 |    82 |            3 | 1 3,1 4,1 5 |
|    1 |    1 |    15 |    90 |            1 | 1 1         |
|    2 |    1 |    17 |    90 |            1 | 2 1         |
+------+------+-------+-------+--------------+-------------+
5 rows in set (0.00 sec)

对于字段product_data – 第二个查询

MariaDB [demos]> select * , count(*) as product_data_count,GROUP_CONCAT(key1,' ',key2) as product_data_ids from 
    -> (
    -> SELECT distinct 
    -> if(b.product_data IS NOT NULL,a.key1,null) AS `key1`,
    -> if(b.product_data IS NOT NULL,a.key2,null) AS `key2`,
    -> if(b.product_data IS NOT NULL,a.info1,null) AS `info1`, 
    ->         if(b.product_data IS NOT NULL,a.info2,null) AS `info2`
    -> FROM main_info a inner join product1 b on  a.key1 = b.key1 AND a.key2= b.key2
    -> ) as sub group by info1,info2
    -> ;
+------+------+-------+-------+--------------------+------------------+
| key1 | key2 | info1 | info2 | product_data_count | product_data_ids |
+------+------+-------+-------+--------------------+------------------+
|    1 |    2 |    14 |    92 |                  1 | 1 2              |
|    1 |    3 |    15 |    82 |                  3 | 1 3,1 4,1 5      |
|    1 |    1 |    15 |    90 |                  1 | 1 1              |
|    2 |    2 |    17 |    90 |                  3 | 2 2,2 3,2 1      |
+------+------+-------+-------+--------------------+------------------+
4 rows in set (0.01 sec)

我希望使用一个查询得到这样的输出,分组为info1,info2

+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| key1 | key2 | info1 | info2 | serial_count | serial_ids  | product_data_count | product_data_ids |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| NULL | NULL |  NULL |  NULL |            1 | NULL        |               NULL | NULL             |
|    1 |    2 |    14 |    92 |            1 | 1 2         |                  1 | 1 2              |
|    1 |    3 |    15 |    82 |            3 | 1 3,1 4,1 5 |                  3 | 1 3,1 4,1 5      |
|    1 |    1 |    15 |    90 |            1 | 1 1         |                  1 | 1 1              |
|    2 |    1 |    17 |    90 |            1 | 2 1         |                  3 | 2 2,2 3,2 1      |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+

下面是表格的结构

DROP TABLE IF EXISTS `main_info`;
CREATE TABLE `main_info` (
  `key1` int(11) NOT NULL,
  `key2` int(11) NOT NULL,
  `info1` int(11) NOT NULL,
  `info2` int(11) NOT NULL,
  `date` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


LOCK TABLES `main_info` WRITE;
INSERT INTO `main_info` VALUES (1,1,15,90,20120501),(1,2,14,92,20120601),(1,3,15,82,20120801),(1,4,15,82,20120801),(1,5,15,82,20120802),(2,1,17,90,20130302),(2,2,17,90,20130302),(2,3,17,90,20130302),(2,4,16,88,20130601);
UNLOCK TABLES;


DROP TABLE IF EXISTS `product1`;
CREATE TABLE `product1` (
  `key1` int(11) NOT NULL,
  `key2` int(11) NOT NULL,
  `serial` int(11) NOT NULL,
  `product_data` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


LOCK TABLES `product1` WRITE;
INSERT INTO `product1` VALUES (1,1,0,'NaN'),(1,1,1,'NaN'),(1,1,2,'NaN'),(1,1,3,'NaN'),(1,2,0,'12.556'),(1,2,1,'13.335'),(1,3,1,'NaN'),(1,3,2,'13.556'),(1,3,3,'14.556'),(1,4,3,'NaN'),(1,5,3,'NaN'),(2,1,0,'12.556'),(2,1,1,'13.553'),(2,1,2,'NaN'),(2,2,12,'129'),(2,3,22,'NaN');
UNLOCK TABLES;

有人请帮助我在一个查询中得到结果.

解决方法:

试试这个

SELECT 
     key1, key2, info1, info2, 
     SUM(Scount) AS serial_count, GROUP_CONCAT(Skey1, ' ', Skey2) AS serial_ids,
     SUM(Pcount) AS product_data_count, GROUP_CONCAT(Pkey1, ' ', Pkey2) AS product_data_ids 
FROM 
(

   SELECT DISTINCT 
     IF(b.serial  < 10 OR b.product_data IS NOT NULL,a.key1, NULL) AS `key1`,
     IF(b.serial  < 10 OR b.product_data IS NOT NULL,a.key2, NULL) AS `key2`,
     IF(b.serial  < 10 OR b.product_data IS NOT NULL,a.info1, NULL) AS `info1`, 
     IF(b.serial  < 10 OR b.product_data IS NOT NULL,a.info2, NULL) AS `info2`,
     IF(b.serial  < 10,a.key1, NULL) AS `Skey1`,
     IF(b.serial  < 10,a.key2, NULL) AS `Skey2`,
     IF(b.product_data IS NOT NULL,a.key1, NULL) AS `Pkey1`,
     IF(b.product_data IS NOT NULL,a.key2, NULL) AS `Pkey2`,
     IF(b.serial < 10, 1, NULL) AS `Scount`,
     IF(b.product_data IS NOT NULL, 1, NULL) AS `Pcount`
   FROM main_info a INNER JOIN product1 b ON  a.key1 = b.key1 AND a.key2= b.key2

   UNION ALL

   SELECT DISTINCT
     NULL AS `key1`,
     NULL AS `key2`,
     NULL AS `info1`,
     NULL AS `info2`,
     NULL AS `Skey1`,
     NULL AS `Skey2`,
     NULL AS `Pkey1`,
     NULL AS `Pkey2`,
     IF(serial > 9, 1, NULL) AS `Scount`,
     IF(product_data IS NULL, 1, NULL) AS `Pcount`
   FROM product1 WHERE serial > 9 xor product_data IS NULL

) AS sub GROUP BY info1,info2

结果(来自问题的数据)

+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| key1 | key2 | info1 | info2 | serial_count | serial_ids  | product_data_count | product_data_ids |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| NULL | NULL | NULL  | NULL  | 1            | NULL        | NULL               | NULL             |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 1    | 2    | 14    | 92    | 1            | 1 2         | 1                  | 1 2              |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 1    | 3    | 15    | 82    | 3            | 1 3,1 4,1 5 | 3                  | 1 3,1 4,1 5      |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 1    | 1    | 15    | 90    | 1            | 1 1         | 1                  | 1 1              |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+

结果(评论数据)

+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| key1 | key2 | info1 | info2 | serial_count | serial_ids  | product_data_count | product_data_ids |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| NULL | NULL | NULL  | NULL  | 1            | NULL        | 1                  | NULL             |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 1    | 2    | 14    | 92    | 1            | 1 2         | 1                  | 1 2              |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 1    | 3    | 15    | 82    | 3            | 1 3,1 4,1 5 | 3                  | 1 3,1 4,1 5      |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 1    | 1    | 15    | 90    | 1            | 1 1         | 1                  | 1 1              |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 2    | 4    | 16    | 88    | 1            | 2 4         | 1                  | 2 4              |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 2    | 1    | 17    | 90    | NULL         | NULL        | 3                  | 2 1,2 2,2 3      |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+

注意：

关于问题背后的基本逻辑,我可以真正理解一些东西,所以答案主要基于预期的结果.例如,如果组字段(info1和info2)为null,则另一个结果将始终为null,除了serial_count和product_data_count(可以为1或null),您是否真的想要获得该结果？请注意,此答案使用UNION ALL的另一个子查询来满足该要求.

内容总结

以上是互联网集市为您收集整理的重复键的Mysql group_concat和1个查询中多列重复的计数(查询优化)全部内容，希望文章能够帮你解决重复键的Mysql group_concat和1个查询中多列重复的计数(查询优化)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/911499.html

来源：【匿名】