合并两个具有相同 ID 的数据库行

debugcn 发表于 Dev

富

嗨，我有以下 mysql 表

如何合并以红色突出显示的两行？

这是我的存储过程代码：

BEGIN

    SELECT

    GROUP_CONCAT(
    CONCAT("MAX(IF(km_kondomanager_millesimal_table_value_table_id='", km_kondomanager_millesimal_table_value_table_id, "',km_kondomanager_millesimal_table_millesimal_value ,0.00)) AS '", km_kondomanager_millesimal_table_name, "'"), "
"
      )INTO @answers
    FROM (
      SELECT DISTINCT km_kondomanager_millesimal_table_value_table_id, km_kondomanager_millesimal_table_name FROM km_kondomanager_millesimal_table_values INNER JOIN km_kondomanager_millesimal_table
                 ON km_kondomanager_millesimal_table_values . km_kondomanager_millesimal_table_value_table_id = km_kondomanager_millesimal_table. km_kondomanager_millesimal_table_id

        WHERE km_kondomanager_millesimal_table_value_group_id = km_group
    ) A;

    SET @query := 
      CONCAT(
        'SELECT km_kondomanager_millesimal_table_value_building_id AS "Flat ID", km_kondomanager_building_unit_code AS "Building Code", CONCAT_WS(" ", km_user_first_name,  km_user_last_name) AS "Proprietario", ', @answers, 

'FROM km_kondomanager_millesimal_table_values 
INNER JOIN km_kondomanager_building_units
ON km_kondomanager_millesimal_table_values . km_kondomanager_millesimal_table_value_building_id = km_kondomanager_building_units. km_kondomanager_building_unit_id 
LEFT JOIN km_kondomanager_building_unit_owners 
ON km_kondomanager_millesimal_table_values . km_kondomanager_millesimal_table_value_building_id = km_kondomanager_building_unit_owners. km_kondomanager_building_unit_owner_building_unit_id 
LEFT JOIN km_users 
ON km_kondomanager_building_unit_owners . km_kondomanager_building_unit_owner_id = km_users. km_user_id
WHERE km_kondomanager_millesimal_table_value_group_id = ',km_group,' GROUP BY km_kondomanager_millesimal_table_value_building_id, km_user_first_name, km_user_last_name'
      );

    PREPARE statement FROM @query;
    EXECUTE statement;
END

这就是我想要实现的目标

富

好的，所以我相信我已经找到了问题的解决方案，这要归功于 @nbk

BEGIN
    SELECT

    GROUP_CONCAT(
    CONCAT("MAX(IF(km_kondomanager_millesimal_table_value_table_id='", km_kondomanager_millesimal_table_value_table_id, "',km_kondomanager_millesimal_table_millesimal_value ,0.00)) AS '", km_kondomanager_millesimal_table_name, "'"), "
"
      )INTO @answers
    FROM (
      SELECT DISTINCT km_kondomanager_millesimal_table_value_table_id, km_kondomanager_millesimal_table_name FROM km_kondomanager_millesimal_table_values INNER JOIN km_kondomanager_millesimal_table
                 ON km_kondomanager_millesimal_table_values . km_kondomanager_millesimal_table_value_table_id = km_kondomanager_millesimal_table. km_kondomanager_millesimal_table_id

        WHERE km_kondomanager_millesimal_table_value_group_id = km_group
    ) A;

    SET @query := 
      CONCAT(
        'SELECT km_kondomanager_millesimal_table_value_building_id AS "Building ID", km_kondomanager_building_unit_code AS "Building Code", group_concat(DISTINCT CONCAT_WS(" ", km_user_first_name,  km_user_last_name)) AS "Proprietario", ', @answers, 
        ' FROM km_kondomanager_millesimal_table_values 
          INNER JOIN km_kondomanager_building_units
                 ON km_kondomanager_millesimal_table_values . km_kondomanager_millesimal_table_value_building_id = km_kondomanager_building_units. km_kondomanager_building_unit_id 
          LEFT JOIN km_kondomanager_building_unit_owners 
                 ON km_kondomanager_millesimal_table_values . km_kondomanager_millesimal_table_value_building_id = km_kondomanager_building_unit_owners. km_kondomanager_building_unit_owner_building_unit_id
                 LEFT JOIN km_users 
                 ON km_kondomanager_building_unit_owners . km_kondomanager_building_unit_owner_id = km_users. km_user_id
          WHERE km_kondomanager_millesimal_table_value_group_id = ',km_group,' GROUP BY km_kondomanager_millesimal_table_value_building_id'
      );

    PREPARE statement FROM @query;
    EXECUTE statement;
END

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。