Mysql group_concat of repeated keys and the number of repetitions of several columns in 1 query (query optimization)

Question

This question is about query optimization to avoid multiple calls to the database via PHP.

So, here is the scenario, I have two tables, one of which contains information that you can call a reference table, and the other - a data table, the key1 and key2 fields are common in both tables based on these fields. Join them.

I don’t know if the request can be made even simpler than what I am doing right now, what I want to achieve:

I would like to find an excellent key1,key2,info1,info2 from the main_info table, when the serial value is less than 10 and key1,key2 both tables, and then group them by info1,info2 , while grouping count duplicate key1,key2 for duplicate info1,info2 fields info1,info2 and group_concat these keys

The contents of the main_info table

 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) 

The contents of the 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) 

Through PHP, I group the info1 and info2 fields of the info1 table, in the current context serial , product_data the product1 table, several times one after another (here I run the query twice, as you can see)

For serial field - 1st request

 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) 

For product_data field - 2nd request

 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) 

I would like to get this conclusion using one query: 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 | | 2 | 1 | 17 | 90 | 1 | 2 1 | 3 | 2 2,2 3,2 1 | +------+------+-------+-------+--------------+-------------+--------------------+------------------+ 

The table structure is shown below.

 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 get the result in one query.

Answer 1

try this one

 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 

RESULT (data from the question)

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

RESULT (data from the comment)

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

Note:

There is something that I can really understand about the underlying logic underlying the question, so the answer is mainly based on the expected result. For example, if the group field ( info1 and info2 ) is null, the other result will always be null, except for serial_count and product_data_count , which can be 1 or null, did you really want to get this? Note that this answer uses a different subquery with UNION ALL to satisfy this.

Answer 2

How to combine your two queries with JOIN?

SQL:

  SELECT tbl1.key1, tbl1.key2, tbl1.info1, tbl1.info2, tbl1.serial_count, tbl1.serial_ids, tbl2.product_data_count, tbl2.product_data_ids FROM ( 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 ) tbl1 LEFT OUTER JOIN ( 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 ) tbl2 ON tbl1.info1 = tbl2.info1 AND tbl1.info2 = tbl2.info2 ORDER BY 3,4 ; 

Output:

 mysql> SELECT -> tbl1.key1, tbl1.key2, tbl1.info1, tbl1.info2, tbl1.serial_count, tbl1.serial_ids, -> tbl2.product_data_count, tbl2.product_data_ids -> FROM -> ( -> 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 -> ) tbl1 -> LEFT OUTER JOIN -> ( -> 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 -> ) tbl2 -> ON tbl1.info1 = tbl2.info1 AND tbl1.info2 = tbl2.info2 -> ORDER BY 3,4 -> ; +------+------+-------+-------+--------------+-------------+--------------------+------------------+ | 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 | +------+------+-------+-------+--------------+-------------+--------------------+------------------+ 5 rows in set (0.01 sec) mysql> select version(); +-----------------+ | version() | +-----------------+ | 10.1.10-MariaDB | +-----------------+ 1 row in set (0.00 sec) 

Answer 3

Judging by your quote, it seems to me that you want to do something like this ( SQLfiddle ):

 SELECT m.info1, m.info2, COUNT(DISTINCT CONCAT(m.key1, ' ', m.key2)) key_count, GROUP_CONCAT(DISTINCT CONCAT(m.key1, ' ', m.key2) ORDER BY m.key1, m.key2) key_pairs, COUNT(DISTINCT p.serial) serial_count, GROUP_CONCAT(DISTINCT p.serial ORDER BY p.serial) serials, COUNT(DISTINCT p.product_data) data_count, GROUP_CONCAT(DISTINCT p.product_data ORDER BY p.product_data) product_data FROM main_info m INNER JOIN product1 p ON p.key1 = m.key1 AND p.key2 = m.key2 WHERE p.serial < 10 GROUP BY m.info1, m.info2 

Count the various values ​​and list them, is this correct? You cannot simply group by info1, info2, and also have columns for key1 or key2 as a result (e.g. min (key1) or max (key2)). I adjusted this in the query above, although it is very different from your result, it may be what you really need, perhaps with a few changes.