mysql group_concat重复键和1次查询中多列重复的次数(查询优化) [英] Mysql group_concat of repeated keys and count of repetition of multiple columns in 1 query ( Query Optimization )

查看：167
本文介绍了mysql group_concat重复键和1次查询中多列重复的次数(查询优化)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这个问题与查询优化有关，以避免通过PHP多次调用数据库.

This question is regarding query optimization to avoid multiple call to database via PHP.

因此，在这种情况下，我有两个表，一个包含可以称为参考表的信息，另一个是数据表，两个表中的字段key1和key2在这两个表中都是通用的，基于这些字段，我们可以加入他们.

So Here is scenario, I have two tables one contains information you can call this as reference table and another one is data table, fields key1 and key2 are common in both table, based on these fields, we can join them.

我不知道查询是否可以比我现在做的还要简单，我想要实现的目标如下:

I don't know whether query can be made even simpler than what I am doing right now, what I want to achieve is as follows :

我想找到与main_info不同的key1,key2,info1,info2 表，只要序列值小于10并且两者的key1,key2 表匹配项，然后在分组时按info1,info2将其分组 计算重复的key1,key2对于info1,info2字段的重复项 和group_concat这些键

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

Contents of table 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

Contents of table 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 I在当前上下文serial，表product1的product_data中将表main_info的字段info1和info2分组，一次又一次(我正在运行)可以看到两次查询)

Via PHP I group fields info1 and info2 of table main_info, in current context serial,product_data of table product1, multiple times one after another (here I am running query twice as you can see)

对于字段serial-第一个查询

For field serial - 1st query

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-第二个查询

For field product_data - 2nd query

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)

我想使用一个查询(按信息1，信息2分组)来获得这样的输出 

+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| 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;

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

Someone please help me to get result in one query.

推荐答案

尝试这个

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)为空，则其他结果将始终为空，除了serial_count和product_data_count可以为1或为空，您真的要得到它吗?请注意，此答案使用另一个带有UNION ALL的子查询来满足该要求.

There is something that I can really understand about the base logic behind the question, so answer mainly base on expected result. Such as if group field (info1 and info2) are null, the other result will always null except for serial_count and product_data_count that can be 1 or null, did you really meant to get that? Notice that this answer use another sub query with UNION ALL to satisfy that.

这篇关于mysql group_concat重复键和1次查询中多列重复的次数(查询优化)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆