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

coder 2023-10-05 原文

这个问题是关于查询优化，以避免通过 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，我在当前上下文 serial 中对表 main_info 的字段 info1 和 info2 进行分组,表product1的product_data，一个接一个多次(如你所见，这里我运行了两次查询)

对于字段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)

我想使用一个查询获得这样的输出，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      |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+

表格结构如下

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;

有人请帮助我在一次查询中获得结果。

最佳答案

如何将您的两个查询与 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
 ;

输出:

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)

关于重复键的Mysql group_concat和1个查询中多列的重复计数(查询优化)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35538092/

多列 group_concat info key product mysql sql join mariadb

有关重复键的Mysql group_concat和1个查询中多列的重复计数(查询优化)的更多相关文章

ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby - 如果指定键的值在数组中相同，如何合并哈希 - 2
我有一个这样的哈希数组:[{:foo=>2,:date=>Sat,01Sep2014},{:foo2=>2,:date=>Sat,02Sep2014},{:foo3=>3,:date=>Sat,01Sep2014},{:foo4=>4,:date=>Sat,03Sep2014},{:foo5=>5,:date=>Sat,02Sep2014}]如果:date相同，我想合并哈希值。我对上面数组的期望是:[{:foo=>2,:foo3=>3,:date=>Sat,01Sep2014},{:foo2=>2,:foo5=>5:date=>Sat,02Sep2014},{:foo4=>4,:dat
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
ruby - 检查字符串是否包含散列中的任何键并返回它包含的键的值 - 2
我有一个包含多个键的散列和一个字符串，该字符串不包含散列中的任何键或包含一个键。h={"k1"=>"v1","k2"=>"v2","k3"=>"v3"}s="thisisanexamplestringthatmightoccurwithakeysomewhereinthestringk1(withspecialcharacterslike(^&*$#@!^&&*))"检查s是否包含h中的任何键的最佳方法是什么，如果包含，则返回它包含的键的值？例如，对于上面的h和s的例子，输出应该是v1。编辑:只有字符串是用户定义的。哈希将始终相同。最佳答案
ruby-on-rails - Ruby on Rails 计数器缓存错误 - 2
尝试在我的RoR应用程序中实现计数器缓存列时出现错误Unknownkey(s):counter_cache。我在这个问题中实现了模型关联:Modelassociationquestion这是我的迁移:classAddVideoVotesCountToVideos0Video.reset_column_informationVideo.find(:all).eachdo|p|p.update_attributes:videos_votes_count,p.video_votes.lengthendenddefself.downremove_column:videos,:video_vot
ruby - 使用多个数组创建计数 - 2
我正在尝试按0-9和a-z的顺序创建数字和字母列表。我有一组值value_array=['0','1','2','3','4','5','6','7','8','9','a','b'，'光盘'，'e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','','u','v','w','x','y','z']和一个组合列表的数组，按顺序，这些数字可以产生x个字符，比方说三个list_array=[]和一个当前字母和数字组合的数组(在将它插入列表数组之前我会把它变成一个字符串，]current_combo['0','0','0']
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
ruby-on-rails - Rails 单选按钮 - 模型中多列的一种选择 - 2
我希望用户从一个模型的三个选项中选择一个。即我有一个模型视频，可以被评为正面/负面/未知目前我有三列bool值(pos/neg/unknown)。这是处理这种情况的最佳方式吗？为此，表单应该是什么样的？目前我有类似的东西但显然它允许多项选择，而我试图将它限制为只有一个..怎么办？最佳答案如果要使用字符串列，让我们说rating。然后在你的表单中:#...#...它只允许一个选择编辑完全相同但使用radio_button_tag: 关于ruby-on-rails-Rails单选按钮-模
ruby-on-rails - solr 清理查询 - 2
我在Rails上使用带有ruby的solr。一切正常，我只需要知道是否有任何现有代码来清理用户输入，比如以?开头的查询。或* 最佳答案我不知道执行此操作的任何代码，但理论上可以通过查看parsingcodeinLucene来完成并搜索thrownewParseException(只有16个匹配!)。在实践中，我认为您最好只捕获代码中的任何solr异常并显示“无效查询”消息或类似信息。编辑:这里有几个“sanitizer”:http://pivotallabs.com/users/zach/blog/articles/937-s
ruby - 正则表达式 - 保存重复捕获的组 - 2
这就是我做的a="%span.rockets#diamonds.ribbons.forever"a=a.match(/(^\%\w+)([\.|\#]\w+)+/)putsa.inspect这是我得到的#这就是我想要的#帮助？我尝试过但失败了:( 最佳答案通常，您不能获得任意数量的捕获组，但如果您使用扫描，您可以为您想要捕获的每个标记获得一个匹配:a="%span.rockets#diamonds.ribbons.forever"a=a.scan(/^%\w+|\G[.|#]\w+/)putsa.inspect["%span","

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

有关重复键的Mysql group_concat和1个查询中多列的重复计数(查询优化)的更多相关文章

随机推荐