计算多行并将其卷到新列。

Question

我试图计算列中的值，以确定每个行子集的值。更容易解释..。

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列的单个值。

非常感谢，提前！

Answer 1

您可以在聚合中使用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。