mysql - MySQL 用 SELECT 合并单个表中的两个表
问题描述
我在 MySQL 中有两个表,我想合并它将table2
托管、禁用和 disable_reason 列的方式,并从中获取所有结果table1
并使用 JOIN 创建单个统一视图。
我已经厌倦了 UNION、JOIN 等选项,但我相信我还没有接近得到这个结果。
表格1
+---------------------+---------------------+------------+-----+-------------+----------------------------------+----------------------------------+--------------------------------------+-----------+------------+--------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+----------+-----------+-------+---------------------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+---------------+---------------------+---------------------+-------------------+--------+---------+---------------------------------------+------------------+---------+--------------------------------------+--------------+------------------+--------------+--------------+--------------+------------+--------------------------+---------------------+----------+------------------+--------------------+-------------------+---------+--------------+-----------+---------------------------------------+---------+-----------+---------+--------------------+
| created_at | updated_at | deleted_at | id | internal_id | user_id | project_id | image_ref | kernel_id | ramdisk_id | launch_index | key_name | key_data | power_state | vm_state | memory_mb | vcpus | hostname | host | user_data | reservation_id | launched_at | terminated_at | display_name | display_description | availability_zone | locked | os_type | launched_on | instance_type_id | vm_mode | uuid | architecture | root_device_name | access_ip_v4 | access_ip_v6 | config_drive | task_state | default_ephemeral_device | default_swap_device | progress | auto_disk_config | shutdown_terminate | disable_terminate | root_gb | ephemeral_gb | cell_name | node | deleted | locked_by | cleaned | ephemeral_key_uuid |
+---------------------+---------------------+------------+-----+-------------+----------------------------------+----------------------------------+--------------------------------------+-----------+------------+--------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+----------+-----------+-------+---------------------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+---------------+---------------------+---------------------+-------------------+--------+---------+---------------------------------------+------------------+---------+--------------------------------------+--------------+------------------+--------------+--------------+--------------+------------+--------------------------+---------------------+----------+------------------+--------------------+-------------------+---------+--------------+-----------+---------------------------------------+---------+-----------+---------+--------------------+
| 2019-08-09 16:08:52 | 2019-08-09 16:10:45 | NULL | 873 | NULL | b6aa784c1e8f448fbbdb17edcbd6c48c | 1d32536d017b42e0a6108cc85764b10f | cb7df5c1-c6eb-40e9-9fc0-78e00bc675f3 | | | 0 | jmp1-ssh-key | ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC97GB8C8s4VxGDUj7Cc5v0NhfbycZlOVtCOIXqbZ4oJCtdDYESc+ZbHCyjiEmJRVqdPNN1wRDIkEEaw38tFgRVAbxVfxD6iQSC5i76lq0AseCzFoqTAcmx1PhIFTvL58S9yuoU4CwrB2Nh4sSPycG+ibM6ttLU1vyOIERTNP7QVAscXdC5gw44TXU5PFPlUSO+wOaBZFRP63+57uDPXVJSYFB8aeCxEdGH1XTYXi1+WcIVFCEBTV6GOFhfjUrUauq2bCEybBN+F7SSxPErDeFiozuHCErMVOIteflTXTUVa2o1I6TRN9KFCBSqc/ht3wYMahAcRkC10PRYrrtyQTY/ root@jmp-xx-010101-1.foooo.net | 1 | active | 8192 | 4 | www01-ssss-010101-1 | ostack-compute-bld-gen-2-52.foooo.net | I2Nsb3VkLWNvbmZpZwpob3N0bmFtZToga2Fma2Etc3NyZC0wMTAxMDEtMQpmcWRuOiBrYWZrYS1zc3JkLTAxMDEwMS0xLnYxdjB4Lm5ldAojIFJlZ2lzdGVyIG5hbWUgaW4gRE5TCnJ1bmNtZDoKICAtIGN1cmwgaHR0cDovL3l1bTUudml2b3guY29tL29wZW5zdGFjay9wZG5zLW9wZW5zdGFjay5zaCAtbyAvdG1wL3BkbnMtb3BlbnN0YWNrLnNoCiAgLSBzaCAvdG1wL3BkbnMtb3BlbnN0YWNrLnNoCiAgLSBjdXJsIGh0dHA6Ly95dW01LnZpdm94LmNvbS9vcGVuc3RhY2svdml2b3gtcHVwcGV0NC1wcm9kdWN0cy5yZXBvIC1vIC9ldGMveXVtLnJlcG9zLmQvdml2b3gtcHVwcGV0NC1wcm9kdWN0cy5yZXBvCiAgLSBob3N0bmFtZWN0bCBzZXQtaG9zdG5hbWUga2Fma2Etc3NyZC0wMTAxMDEtMS52MXYweC5uZXQ= | r-rrtwqbl5 | 2019-08-09 16:10:45 | NULL | www01-ssss-010101-1 | NULL | general | 0 | NULL | ostack-compute-bld-gen-2-52.foooo.net | 36 | NULL | bd3e335f-e0c7-4ee6-bde1-bb07e88b9ef8 | NULL | /dev/sda | NULL | NULL | | NULL | NULL | /dev/sdb | 0 | 1 | 0 | 0 | 80 | 0 | NULL | ostack-compute-bld-gen-2-52.foooo.net | 0 | NULL | 0 | NULL |
+---------------------+---------------------+------------+-----+-------------+----------------------------------+----------------------------------+--------------------------------------+-----------+------------+--------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+----------+-----------+-------+---------------------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+---------------+---------------------+---------------------+-------------------+--------+---------+---------------------------------------+------------------+---------+--------------------------------------+--------------+------------------+--------------+--------------+--------------+------------+--------------------------+---------------------+----------+------------------+--------------------+-------------------+---------+--------------+-----------+---------------------------------------+---------+-----------+---------+--------------------+
表2
+---------------------+---------------------+------------+-----+---------------------------------------+--------------+---------+--------------+----------+---------+-----------------+---------------------+-------------+---------+--------------------------------------+
| created_at | updated_at | deleted_at | id | host | binary | topic | report_count | disabled | deleted | disabled_reason | last_seen_up | forced_down | version | uuid |
+---------------------+---------------------+------------+-----+---------------------------------------+--------------+---------+--------------+----------+---------+-----------------+---------------------+-------------+---------+--------------------------------------+
| 2019-08-07 02:23:06 | 2019-08-23 19:09:59 | NULL | 591 | ostack-compute-bld-gen-2-52.foooo.net | nova-compute | compute | 144265 | 0 | 0 | NULL | 2019-08-23 19:09:59 | 0 | 37 | 2f8e8f59-0e96-434c-a671-6170dffb1043 |
+---------------------+---------------------+------------+-----+---------------------------------------+--------------+---------+--------------+----------+---------+-----------------+---------------------+-------------+---------+--------------------------------------+
解决方案
为了加入这个,您需要在两个表之间有某种通用标识符。在您的示例中,有一个名为 的字段id
,但行似乎不共享相同的id
内容-出于此问题的目的,我假设这是一个截断集,而id
s 不匹配的事实是疏忽.
select * from table1, table2 where table1.id = table2.id
您可以在其他匹配字段上执行相同的操作,例如node
如果这是一个更好的共享密钥:
select * from table1, table2 where table1.node = table2.node
无论哪种方式,您都必须查看数据并决定如何匹配行以影响连接。
推荐阅读
- javascript - 为什么 JavaScript 中的这个生成器函数在任何 yield 表达式之前停止?
- rust - 如何对关联类型的关联类型(例如 Iterator::Item)施加类型约束?
- windows-subsystem-for-linux - 从 WSL 使 .exe / .cmd / .bat 可执行
- android - 如何找到 androidx.browser:browser 版本?
- javascript - 如何使用 JSNetworkX 在实际节点之外获取标签?
- c - 为什么这个 C 程序不让我访问内存?
- sql - 使用另一台计算机上的 bak 文件还原数据库
- php - 使用 PHP 在 mySQL 中查找多个用户的帐户
- python - ValueError:使用序列设置数组元素。同时将 np 数组转换为 float32
- r - 为什么使用 glmnet 模型时 R 中的“预测”会返回高维预测?