首页 \ 问答 \ 如何比较3个表并在mysql中合并它们的列(How to compare 3 tables and merge their columns in mysql)

如何比较3个表并在mysql中合并它们的列(How to compare 3 tables and merge their columns in mysql)

 Sqlfiddle 
 

 这些是我的表; 
 

         mysql> show tables;
         +--------------------+
         | Tables_in_products |
         +--------------------+
         | main_info          |
         | product1           |
         | product2           |
         +--------------------+
         3 rows in set (0.00 sec)

 

 这是我的第一张桌子 
 

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

 

 这是产品表1： 
 

         mysql> select * from product1;
         +------+------+--------+--------------+
         | key1 | key2 | serial | product_data |
         +------+------+--------+--------------+
         | 1    | 1    | 0      | 15.556       |
         | 1    | 1    | 1      | 14.996       |
         | 1    | 1    | 2      | 12.556       |
         | 1    | 1    | 3      | 15.669       |
         | 1    | 2    | 0      | 12.556       |
         | 1    | 2    | 1      | 13.335       |
         | 1    | 3    | 1      | 12.225       |
         | 1    | 3    | 2      | 13.556       |
         | 1    | 3    | 3      | 14.556       |
         | 2    | 1    | 0      | 12.556       |
         | 2    | 1    | 1      | 13.553       |
         | 2    | 1    | 2      | 12.335       |
         +------+------+--------+--------------+
         12 rows in set (0.00 sec)

 

 这是第二个产品表 
 

         mysql> select * from product2;
         +------+------+--------+--------------+
         | key1 | key2 | serial | product_data |
         +------+------+--------+--------------+
         | 1    | 1    | 0      | 5.556        |
         | 1    | 1    | 1      | 4.996        |
         | 1    | 2    | 0      | 2.556        |
         | 1    | 2    | 1      | 3.335        |
         | 1    | 2    | 2      | 2.56         |
         | 1    | 2    | 3      | 3.556        |
         | 1    | 3    | 1      | 2.225        |
         | 1    | 3    | 2      | 3.556        |
         | 2    | 2    | 0      | 2.556        |
         | 2    | 2    | 1      | 3.553        |
         +------+------+--------+--------------+
         10 rows in set (0.00 sec)

 

 我有超过8个产品表，其中我想比较2产品表和main_info表的key1, key2 and serial ，取决于我想要合并的最大值，如果产品数据不存在则替换NaN ，最后想有1输出如下 
 

 预期的O / P. 
 

   key1       key2       serial     info1      info2      date       product_table1_data product_table2_data
   1          1          0          15         90         20120501   15.556     5.556     
   1          1          1          15         90         20120501   14.996     4.996     
   1          1          2          15         90         20120501   12.556     NaN       
   1          1          3          15         90         20120501   15.669     NaN       
   1          2          0          14         92         20120601   12.556     2.556     
   1          2          1          14         92         20120601   13.335     3.335     
   1          2          2          14         92         20120601   NaN        2.56      
   1          2          3          14         92         20120601   NaN        3.556     
   1          3          1          15         82         20120801   12.225     2.225     
   1          3          2          15         82         20120801   13.556     3.556     
   1          3          3          15         82         20120801   14.556     NaN       
   2          1          0          17         90         20130302   12.556     NaN       
   2          1          1          17         90         20130302   13.553     NaN       
   2          1          2          17         90         20130302   12.335     NaN       
   2          2          0          16         88         20130601   NaN        2.556     
   2          2          1          16         88         20130601   NaN        3.553     

 

 请有人帮助我获得预期的结果 
 

 这是我的数据库的结构; 
 

 $ cat product.sql
   --
   -- Table structure for table `main_info`
   --

   DROP TABLE IF EXISTS `main_info`;

   CREATE TABLE `main_info` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `info1` varchar(1000) DEFAULT NULL,
     `info2` varchar(1000) DEFAULT NULL,
     `date` varchar(1000) DEFAULT 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'),('2','1','17','90','20130302'),('2','2','16','88','20130601');
   UNLOCK TABLES;


   DROP TABLE IF EXISTS `product1`;

   CREATE TABLE `product1` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `serial` varchar(1000) DEFAULT NULL,
     `product_data` varchar(1000) DEFAULT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


   LOCK TABLES `product1` WRITE;

   INSERT INTO `product1` VALUES ('1','1','0','15.556'),('1','1','1','14.996'),('1','1','2','12.556'),('1','1','3','15.669'),('1','2','0','12.556'),('1','2','1','13.335'),('1','3','1','12.225'),('1','3','2','13.556'),('1','3','3','14.556'),('2','1','0','12.556'),('2','1','1','13.553'),('2','1','2','12.335');

   UNLOCK TABLES;



   DROP TABLE IF EXISTS `product2`;

   CREATE TABLE `product2` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `serial` varchar(1000) DEFAULT NULL,
     `product_data` varchar(1000) DEFAULT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


   LOCK TABLES `product2` WRITE;

   INSERT INTO `product2` VALUES ('1','1','0','5.556'),('1','1','1','4.996'),('1','2','0','2.556'),('1','2','1','3.335'),('1','2','2','2.56'),('1','2','3','3.556'),('1','3','1','2.225'),('1','3','2','3.556'),('2','2','0','2.556'),('2','2','1','3.553');
   UNLOCK TABLES;

 

 我尝试过这个，但是这并没有考虑每个密钥对和NaN的序列最大值 
 

 SELECT * 
 FROM main_info INNER JOIN product1
 ON 
 main_info.key1=product1.key1 and
 main_info.key2=product1.key2
 INNER JOIN product2 
 ON 
 product1.key1=product2.key1 and
 product1.key2=product2.key2

 

 合并流程细节：回答评论 
 

 1。 从main_info表2中取出key1和key2。 在表product1，product2中搜索，如果在表中的任何一个表或两个表中都找到key1和key2，则获取这些键的串行列的长度，找到最大长度， 
 

 例如在product1表中，我们有4个序列号 
 

     +------+------+--------+--------------+
     | key1 | key2 | serial | product_data |
     +------+------+--------+--------------+
     | 1    | 1    | 0      | 15.556       |
     | 1    | 1    | 1      | 14.996       |
     | 1    | 1    | 2      | 12.556       |
     | 1    | 1    | 3      | 15.669       |

 

 在第二个表中，我们只有2个序列号，仅0和1 
 

     +------+------+--------+--------------+
     | key1 | key2 | serial | product_data |
     +------+------+--------+--------------+
     | 1    | 1    | 0      | 5.556        |
     | 1    | 1    | 1      | 4.996        |

 

 所以从表中合并序列号并检查数据是否存在，如果存在，则保持product_date写入NaN 
 

 合并2个产品表时输出应该是这样的，一旦完成，添加对应于key1和key2的info1 info2和date列 
 

     +------+------+--------+--------------+--------------
     | key1 | key2 | serial | product_data |product_data2
     +------+------+--------+--------------+-------------
     | 1    | 1    | 0      | 15.556       |5.556
     | 1    | 1    | 1      | 14.996       |4.996
     | 1    | 1    | 2      | 12.556       |NaN
     | 1    | 1    | 3      | 15.669       |NaN

 

 如果您需要更多信息，请与我们联系 
 

 谢谢。 

Sqlfiddle
 

These are my tables;
 

         mysql> show tables;
         +--------------------+
         | Tables_in_products |
         +--------------------+
         | main_info          |
         | product1           |
         | product2           |
         +--------------------+
         3 rows in set (0.00 sec)

 

This is my first table
 

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

 

This is product table1 :
 

         mysql> select * from product1;
         +------+------+--------+--------------+
         | key1 | key2 | serial | product_data |
         +------+------+--------+--------------+
         | 1    | 1    | 0      | 15.556       |
         | 1    | 1    | 1      | 14.996       |
         | 1    | 1    | 2      | 12.556       |
         | 1    | 1    | 3      | 15.669       |
         | 1    | 2    | 0      | 12.556       |
         | 1    | 2    | 1      | 13.335       |
         | 1    | 3    | 1      | 12.225       |
         | 1    | 3    | 2      | 13.556       |
         | 1    | 3    | 3      | 14.556       |
         | 2    | 1    | 0      | 12.556       |
         | 2    | 1    | 1      | 13.553       |
         | 2    | 1    | 2      | 12.335       |
         +------+------+--------+--------------+
         12 rows in set (0.00 sec)

 

This is second product table
 

         mysql> select * from product2;
         +------+------+--------+--------------+
         | key1 | key2 | serial | product_data |
         +------+------+--------+--------------+
         | 1    | 1    | 0      | 5.556        |
         | 1    | 1    | 1      | 4.996        |
         | 1    | 2    | 0      | 2.556        |
         | 1    | 2    | 1      | 3.335        |
         | 1    | 2    | 2      | 2.56         |
         | 1    | 2    | 3      | 3.556        |
         | 1    | 3    | 1      | 2.225        |
         | 1    | 3    | 2      | 3.556        |
         | 2    | 2    | 0      | 2.556        |
         | 2    | 2    | 1      | 3.553        |
         +------+------+--------+--------------+
         10 rows in set (0.00 sec)

 

I have more than 8 product table, in which I would like to compare key1, key2 and serial, of 2 product table and main_info table, depending on the maximum I would like to merge, if product data is not exist then substitute NaN and finally would like to have 1 output like below
 

Expected O/P
 

   key1       key2       serial     info1      info2      date       product_table1_data product_table2_data
   1          1          0          15         90         20120501   15.556     5.556     
   1          1          1          15         90         20120501   14.996     4.996     
   1          1          2          15         90         20120501   12.556     NaN       
   1          1          3          15         90         20120501   15.669     NaN       
   1          2          0          14         92         20120601   12.556     2.556     
   1          2          1          14         92         20120601   13.335     3.335     
   1          2          2          14         92         20120601   NaN        2.56      
   1          2          3          14         92         20120601   NaN        3.556     
   1          3          1          15         82         20120801   12.225     2.225     
   1          3          2          15         82         20120801   13.556     3.556     
   1          3          3          15         82         20120801   14.556     NaN       
   2          1          0          17         90         20130302   12.556     NaN       
   2          1          1          17         90         20130302   13.553     NaN       
   2          1          2          17         90         20130302   12.335     NaN       
   2          2          0          16         88         20130601   NaN        2.556     
   2          2          1          16         88         20130601   NaN        3.553     

 

Kindly someone help me to get expected result
 

This is structure of my database;
 

 $ cat product.sql
   --
   -- Table structure for table `main_info`
   --

   DROP TABLE IF EXISTS `main_info`;

   CREATE TABLE `main_info` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `info1` varchar(1000) DEFAULT NULL,
     `info2` varchar(1000) DEFAULT NULL,
     `date` varchar(1000) DEFAULT 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'),('2','1','17','90','20130302'),('2','2','16','88','20130601');
   UNLOCK TABLES;


   DROP TABLE IF EXISTS `product1`;

   CREATE TABLE `product1` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `serial` varchar(1000) DEFAULT NULL,
     `product_data` varchar(1000) DEFAULT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


   LOCK TABLES `product1` WRITE;

   INSERT INTO `product1` VALUES ('1','1','0','15.556'),('1','1','1','14.996'),('1','1','2','12.556'),('1','1','3','15.669'),('1','2','0','12.556'),('1','2','1','13.335'),('1','3','1','12.225'),('1','3','2','13.556'),('1','3','3','14.556'),('2','1','0','12.556'),('2','1','1','13.553'),('2','1','2','12.335');

   UNLOCK TABLES;



   DROP TABLE IF EXISTS `product2`;

   CREATE TABLE `product2` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `serial` varchar(1000) DEFAULT NULL,
     `product_data` varchar(1000) DEFAULT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


   LOCK TABLES `product2` WRITE;

   INSERT INTO `product2` VALUES ('1','1','0','5.556'),('1','1','1','4.996'),('1','2','0','2.556'),('1','2','1','3.335'),('1','2','2','2.56'),('1','2','3','3.556'),('1','3','1','2.225'),('1','3','2','3.556'),('2','2','0','2.556'),('2','2','1','3.553');
   UNLOCK TABLES;

 

I tried this, but this is not taking care about serial maximum for each key pair and about NaN
 

 SELECT * 
 FROM main_info INNER JOIN product1
 ON 
 main_info.key1=product1.key1 and
 main_info.key2=product1.key2
 INNER JOIN product2 
 ON 
 product1.key1=product2.key1 and
 product1.key2=product2.key2

 

Merging process detail : Answer for comment
 

1 . take key1 and key2 from main_info table 2 . search in table product1, product2, if key1 and key2 is found in any one of the table or in both the table, get the length of serial column for those keys, find maximum length, 
 

for example in product1 table, we have 4 serial number 
 

     +------+------+--------+--------------+
     | key1 | key2 | serial | product_data |
     +------+------+--------+--------------+
     | 1    | 1    | 0      | 15.556       |
     | 1    | 1    | 1      | 14.996       |
     | 1    | 1    | 2      | 12.556       |
     | 1    | 1    | 3      | 15.669       |

 

and in second table we have just 2 serial numbers, 0 and 1 only
 

     +------+------+--------+--------------+
     | key1 | key2 | serial | product_data |
     +------+------+--------+--------------+
     | 1    | 1    | 0      | 5.556        |
     | 1    | 1    | 1      | 4.996        |

 

so merge serial numbers from both the table and check whether data exists or not if exists then keep product_date else write NaN
 

Output should be like this when you merge 2 product tables, once this is done add info1 info2 and date column corresponding to key1 and key2
 

     +------+------+--------+--------------+--------------
     | key1 | key2 | serial | product_data |product_data2
     +------+------+--------+--------------+-------------
     | 1    | 1    | 0      | 15.556       |5.556
     | 1    | 1    | 1      | 14.996       |4.996
     | 1    | 1    | 2      | 12.556       |NaN
     | 1    | 1    | 3      | 15.669       |NaN

 

Please let me know if you need more information
 

Thank you.

原文：https://stackoverflow.com/questions/24155835
更新时间：2022-09-08 10:09

最满意答案

 如果要删除所有打开和关闭的HTML标记，则可以尝试查找模式</?[^>]+>并用空字符串替换： 
 

x <- "tags : </P></TEXT>  </BODY> <TRAILER> NYT-06-22-98 1759EDT &QL; </TRAILER> </DOC>"
gsub("</?[^>]+>", "", x)


[1] "tags :     NYT-06-22-98 1759EDT &QL;  "

 

 

 演示 
 

 

 作为主要的免责声明，一般来说，您不应该使用正则表达式来解析HTML / XML内容。 在这种特殊情况下，如果您只想剥离所有标签， gsub可能是一个可行的选择。 

If you want to remove all opening and closing HTML tags, then you may try finding the pattern </?[^>]+> and replacing with empty string:
 

x <- "tags : </P></TEXT>  </BODY> <TRAILER> NYT-06-22-98 1759EDT &QL; </TRAILER> </DOC>"
gsub("</?[^>]+>", "", x)


[1] "tags :     NYT-06-22-98 1759EDT &QL;  "

 

 

Demo
 

 

As a major disclaimer, in general you should not use regex to parse HTML/XML content. In this particular case, if you just want to strip off all tags, gsub may be a viable option.

相关问答

更多

相关文章

更多
  • Hadoop中间结果Merge压缩不全bug
  • mysql问题
  • MYSQL使用mysqldump导出某个表的部分数据
  • 通过Sqoop实现Mysql / Oracle 与HDFS / Hbase互导数据
  • MySQL最佳实践
  • 关于netbeans和mysql的问题
  • 关于mysql 的 sql
  • solr 索引mysql DIH 链接
  • MySQL常用操作命令汇总
  • MySQL 5.6 my.cnf 配置文件详解

    • 最新问答

    更多
  • 您如何使用git diff文件，并将其应用于同一存储库的副本的本地分支？(How do you take a git diff file, and apply it to a local branch that is a copy of the same repository?)
  • 将长浮点值剪切为2个小数点并复制到字符数组(Cut Long Float Value to 2 decimal points and copy to Character Array)
  • OctoberCMS侧边栏不呈现(OctoberCMS Sidebar not rendering)
  • 页面加载后对象是否有资格进行垃圾回收？(Are objects eligible for garbage collection after the page loads?)
  • codeigniter中的语言不能按预期工作(language in codeigniter doesn' t work as expected)
  • 在计算机拍照在哪里进入
  • 使用cin.get（）从c ++中的输入流中丢弃不需要的字符(Using cin.get() to discard unwanted characters from the input stream in c++)
  • No for循环将在for循环中运行。(No for loop will run inside for loop. Testing for primes)
  • 单页应用程序：页面重新加载(Single Page Application: page reload)
  • 在循环中选择具有相似模式的列名称(Selecting Column Name With Similar Pattern in a Loop)
  • System.StackOverflow错误(System.StackOverflow error)
  • KnockoutJS未在嵌套模板上应用beforeRemove和afterAdd(KnockoutJS not applying beforeRemove and afterAdd on nested templates)
  • 散列包括方法和/或嵌套属性(Hash include methods and/or nested attributes)
  • android - 如何避免使用Samsung RFS文件系统延迟/冻结？(android - how to avoid lag/freezes with Samsung RFS filesystem?)
  • TensorFlow：基于索引列表创建新张量(TensorFlow: Create a new tensor based on list of indices)
  • 企业安全培训的各项内容
  • 错误：RPC失败;(error: RPC failed; curl transfer closed with outstanding read data remaining)
  • C＃类名中允许哪些字符？(What characters are allowed in C# class name?)
  • NumPy：将int64值存储在np.array中并使用dtype float64并将其转换回整数是否安全？(NumPy: Is it safe to store an int64 value in an np.array with dtype float64 and later convert it back to integer?)
  • 注销后如何隐藏导航portlet？(How to hide navigation portlet after logout?)
  • 将多个行和可变行移动到列(moving multiple and variable rows to columns)
  • 提交表单时忽略基础href，而不使用Javascript(ignore base href when submitting form, without using Javascript)
  • 对setOnInfoWindowClickListener的意图(Intent on setOnInfoWindowClickListener)
  • Angular $资源不会改变方法(Angular $resource doesn't change method)
  • 在Angular 5中不是一个函数(is not a function in Angular 5)
  • 如何配置Composite C1以将.m和桌面作为同一站点提供服务(How to configure Composite C1 to serve .m and desktop as the same site)
  • 不适用：悬停在悬停时：在元素之前[复制](Don't apply :hover when hovering on :before element [duplicate])
  • 常见的python rpc和cli接口(Common python rpc and cli interface)
  • Mysql DB单个字段匹配多个其他字段(Mysql DB single field matching to multiple other fields)
  • 产品页面上的Magento Up出售对齐问题(Magento Up sell alignment issue on the products page)

    • Copyright ©2023 peixunduo.com All Rights Reserved.粤ICP备14003112号

    本站部分内容来源于互联网，仅供学习和参考使用，请莫用于商业用途。如有侵犯你的版权，请联系我们(neng862121861#163.com)，本站将尽快处理。谢谢合作！