这个问题是关于查询优化以避免通过 PHP 多次调用数据库。
所以这是场景，我有两个表，一个包含您可以将其称为参考表的信息，另一个是数据表、字段key1 and key2这两个表都是通用的，根据这些字段，我们可以将它们连接起来。
我不知道查询是否可以比我现在正在做的更简单，我想要实现的目标如下：
我想找到独特的key1,key2,info1,info2 from main_info表，只要序列值小于 10 并且key1,key2两者的
表匹配，然后将它们分组info1,info2, 分组时
统计重复次数key1,key2对于重复项info1,info2领域
和group_concat那些钥匙
表的内容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 我对字段进行分组info1 and info2表的main_info，在当前上下文中serial,product_data表的product1，接连多次（如您所见，这里我运行查询两次）
对于领域serial- 第一个查询
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;
请有人帮助我在一次查询中获得结果。
