时间戳

首页 > 解决方案 > 如何将转换后的行汇总并集中到一个具有相同 ID 的字段中？

mysql - 如何将转换后的行汇总并集中到一个具有相同 ID 的字段中？

问题描述

我目前坚持使用 concat 具有相同 vm id 的两行我的原始 sql 数据看起来像这样 vm_info vm_disk

我已将“数据存储”从列转换为行这是我的 sql 查询，

SELECT vm.vm_info.id, 
    vm.vm_info.vm,
    vm.vm_info.vm_folder, 
  vm.vm_info.name, 
   vm.vm_info.cpu_count, 
    format(vm.vm_info.memory_size_MiB/1024,0) AS Memory_size_GiB, 
   vm_disk_convert.capacity,
    vm_disk_convert.NAS,
    vm_disk_convert.vSan,
    vm_disk_convert.vSan_intra,
    vm.vm_info.power_state, 
    vm.vm_info.modify_date
FROM vm.vm_info, (
    SELECT `vm`, `capacity`,
    case when vm.vm_disk.datastores = "NFS-SA3400" then vm.vm_disk.datastores END AS NAS,
        case when vm.vm_disk.datastores = "vsanDatastore" then vm.vm_disk.datastores END as vSan,
        case when vm.vm_disk.datastores = "vsanDatastore-Intra" then vm.vm_disk.datastores END as vSan_Intra 
    FROM vm.vm_disk
) AS vm_disk_convert
WHERE vm.vm_info.vm = vm_disk_convert.vm
ORDER BY vm.vm_info.vm_folder, vm.vm_info.vm;

我当前的 sql 结果看起来像我用 group_concat 尝试过的这个 sql 结果 ，但结果显示如下

如何将具有相同 vm id 的行集中到一个字段中？ 预期结果 感谢您帮助我！

标签： mysqlsqlconcatenationgroup-concat

解决方案

你可以使用 GROUP BY 语句，它会是这样的：

    SELECT vm.vm_info.id, 
    vm.vm_info.vm,
    vm.vm_info.vm_folder, 
  vm.vm_info.name, 
   vm.vm_info.cpu_count, 
    format(vm.vm_info.memory_size_MiB/1024,0) AS Memory_size_GiB, 
   vm_disk_convert.capacity,
    vm_disk_convert.NAS,
    vm_disk_convert.vSan,
    vm_disk_convert.vSan_intra,
    vm.vm_info.power_state, 
    vm.vm_info.os, 
    vm.vm_info.modify_date
FROM vm.vm_info, (
    SELECT `vm`, `capacity`,
    case when vm.vm_disk.datastores = "NFS-SA3400" then vm.vm_disk.datastores END AS NAS,
        case when vm.vm_disk.datastores = "vsanDatastore" then vm.vm_disk.datastores END as vSan,
        case when vm.vm_disk.datastores = "vsanDatastore-Intra" then vm.vm_disk.datastores END as vSan_Intra 
    FROM vm.vm_disk
) AS vm_disk_convert
GROUP BY `vm`
ORDER BY vm.vm_info.vm_folder, vm.vm_info.vm;

此语句完全符合您的要求...如果我写错了，对不起，我会给您一个教程的链接，您可以在您的项目中使用该教程：https ://www.w3schools.com/sql /sql_groupby.asp

推荐阅读