代码之家 › 专栏 › 技术社区 › JonWay

在具有条件的同一列中基于条件创建新列状态

sql-server-2008 tsql sql-server

JonWay · 技术社区 · 6 年前

我有以下数据

DECLARE @TBL TABLE (CustomerID INT, CustomerName VARCHAR(100),Product VARCHAR(100), Status_Col VARCHAR(100))
-- INSERT DATA
INSERT INTO @TBL VALUES     (90,'Wilman Kala','Dairy','Simple'),
    (90,'Wilman Kala','Grain','Other'), (90,'Wilman Kala','Dairy','Other'), (81,'Hipermercados','Produce','Simple'),
    (81,'Hipermercados','Produce','Other'), (34,'Hanari Carnes','Seafood','Simple'),(34,'Hanari Carnes','Produce','Simple'),
    (34,'Hanari Carnes','Condiments','Simple'), (85,'Smith stock','Grains','Other'),    (86,'Jane will','Cereals','Other'),
    (87,'Victuailles en stock','Condiments','Simple'),  (88,'SuprÃªmes dÃ©lices','Confections','Other'),
    (88,'SuprÃªmes dÃ©lices','Dairy','Other'),    (89,'Marry Dawson','Dairy','Simple'),(90,'Eve Lawson','Dairy','Other'),
    (91,'Hanari Carnes','Beverages','Other'),   (91,'Hanari Carnes','Confections','Other'),
    (92,'Mark Johnson','Beverages','Other');

我想创建一个新的列,显示拥有simple的客户,然后是simple from Status_Col ,如果他们有其他,那么其他。但是,如果他们在任何阶段都有simple或other的Status\u Col列,那么我想要 NewStatus Other/Simple . In my data CustomerID 81 and 90 should show 其他/简单

我试过下面的问题,它没有给我正确的答案

SELECT CustomerID,CustomerName,Product,
Status_Col,
CASE WHEN Status_Col='Other' THEN 'Other'
     WHEN Status_Col='Simple' THEN 'Simple'
     WHEN Status_Col='Other' OR Status_Col='Simple' THEN 'Other/Simple' END AS NewStatus
FROM @TBL
order by CustomerName

CustomerID  CustomerName            Product         Status_Col  NewStatus
90           Eve Lawson             Dairy              Other      Other
91           Hanari Carnes          Beverages          Other      Other
91           Hanari Carnes          Confections        Other      Other
34           Hanari Carnes          Seafood            Simple     Simple
34           Hanari Carnes          Produce            Simple     Simple
34           Hanari Carnes          Condiments         Simple     Simple
81           Hipermercados          Produce            Simple     Simple
81           Hipermercados          Produce            Other      Other
86           Jane will              Cereals            Other      Other
92           Mark Johnson           Beverages          Other      Other
89           Marry Dawson           Dairy              Simple     Simple
85           Smith stock            Grains             Other      Other
88           SuprÃªmes dÃ©lices       Confections        Other      Other
88           SuprÃªmes dÃ©lices       Dairy              Other      Other
87           Victuailles en stock   Condiments         Simple     Simple
90           Wilman Kala            Dairy              Simple     Simple
90           Wilman Kala            Grain              Other      Other
90           Wilman Kala            Dairy              Other      Other

预期输出

CustomerID  CustomerName            Product         Status_Col  NewStatus
90           Eve Lawson             Dairy              Other      Other
91           Hanari Carnes          Beverages          Other      Other
91           Hanari Carnes          Confections        Other      Other
34           Hanari Carnes          Seafood            Simple     Simple
34           Hanari Carnes          Produce            Simple     Simple
34           Hanari Carnes          Condiments         Simple     Simple
81           Hipermercados          Produce            Simple   Other/Simple
81           Hipermercados          Produce            Other    Other/Simple
86           Jane will              Cereals            Other      Other
92           Mark Johnson           Beverages          Other      Other
89           Marry Dawson           Dairy              Simple     Simple
85           Smith stock            Grains             Other      Other
88           SuprÃªmes dÃ©lices       Confections        Other      Other
88           SuprÃªmes dÃ©lices       Dairy              Other      Other
87           Victuailles en stock   Condiments         Simple     Simple
90           Wilman Kala            Dairy              Simple   Other/Simple
90           Wilman Kala            Grain              Other    Other/Simple
90           Wilman Kala            Dairy              Other    Other/Simple

2 回复 | 直到 6 年前

Zohar Peled 6 年前

SELECT CustomerID,CustomerName,Product,
Status_Col,
CASE WHEN (
            SELECT COUNT(DISTINCT Status_Col) 
            FROM @TBL AS T1 
            WHERE T0.CustomerID = T1.CustomerID 
            AND T0.CustomerName = T1.CustomerName
           ) > 1 THEN 'Other/Simple'
     WHEN Status_Col='Other' THEN 'Other'
     WHEN Status_Col='Simple' THEN 'Simple' END AS NewStatus
FROM @TBL AS T0
order by CustomerName

不过,我想知道,为什么同一个customerId有多个客户名称。

Thom A 6 年前

你的 CASE 表达式没有什么意义(最后的表达式永远不会是真的,因为前面的一个表达式必须已经是真的)。似乎您正在尝试检查其他行的值,而不是作为一个整体引用数据集。

这似乎就是你想要的答案:

SELECT CustomerID,
       CustomerName,
       Product,
       Status_Col,
       CASE WHEN COUNT(CASE Status_Col WHEN 'Simple' THEN 1 END) OVER (PARTITION BY CustomerID, CustomerName) > 0
             AND COUNT(CASE Status_Col WHEN 'Other' THEN 1 END) OVER (PARTITION BY CustomerID, CustomerName) > 0 THEN 'Other/Simple'
            ELSE Status_Col --If both values don't appear, then it must be the current one
        END AS NewStatus
FROM @TBL
order by CustomerName;