时间戳

首页 > 解决方案 > 评估多行并汇总到新列

sql - 评估多行并汇总到新列

问题描述

我正在尝试评估列中的值以确定每个行子集的值。更容易解释...

select distinct
    app_id,
    asset_id,
    asset_migration_scope
from
    table

这是一个输出示例：

app_ID   asset_id   asset_migration_scope

123      asset1     Migrating

123      asset2     Migrating

123      asset3     Not Migrating

456      asset1     Migrating

456      asset2     Migrating

789      asset1     Not Migrating

789      asset2     Not Migrating

我想做的是创建第四列来汇总这些内容。如果给定应用程序 ID 的所有资产 = 正在迁移，则该应用程序 ID 的每一行的值都将为正在迁移。如果它是迁移/不迁移的混合体，我们将其称为混合。都是“不迁移”，那就是“不迁移”

输出如下所示：

app_ID   asset_id   asset_migration_scope    app_migration_scope

123      asset1     Migrating                Hybrid

123      asset2     Migrating                Hybrid

123      asset3     Not Migrating            Hybrid

456      asset1     Migrating                Migrating

456      asset2     Migrating                Migrating

789      asset1     Not Migrating            Not Migrating

789      asset2     Not Migrating            Not Migrating

如何进行此汇总，以便我们查看每个 app_id + asset_id 组合以及相应的asset_migration_scope 值，以根据 app_id 确定 app_migration_scope 列的单个值？

非常感谢提前！

标签： sqlsql-serverdatabase

解决方案

您可以将case表达式与聚合一起使用：

SELECT t.app_id, t.asset_id, t.asset_migration_scope,
       (CASE WHEN mn_scope <> mx_scope 
             THEN 'Hybrid' 
             WHEN (mn_scope = mx_scope AND mn_scope = 'Migrating')
             THEN 'Migrating'
             WHEN (mn_scope = mx_scope AND mn_scope = 'Not Migrating')
             THEN 'Not Migrating'
        END) AS app_migration_scope 
FROM table t CROSS APPLY
     (SELECT MIN(t1.asset_migration_scope) AS mn_scope, MAX(t1.asset_migration_scope) AS mx_scope
      FROM table t1
      WHERE t1.app_ID = t.app_ID
     ) t1;

但是，DISTINCT由于您的示例数据不建议我使用DISTINCT.

推荐阅读