时间戳

首页 > 解决方案 > 使用 CASE 时按列值分组

sql - 使用 CASE 时按列值分组

问题描述

select C.name as Name,
       C.id as ID,         
      (CASE WHEN CA.type_id = 231 and c.id = CA.id  then CA.value end ) as PGroup,
      (CASE WHEN  CA.type_id IN ( 32, 38, 40, 52 ) and c.id = CA.id  then CA.value end )as Facilty,
      TC.cname as firmName
        from tbCases C
    join tbCompanies TC on TC.company_id = C.lawfirm_id
    join tbAttributes CA on C.case_id = CA.case_id 
    where C.client_id = 9026 and c.id = 812429

我的结果

Name      ID        PGroup        Facilty     firmName
WA-SPOK   812429    NULL          SPOKANE     CUMMINGS LLP
WA-SPOK   812429    NULL          VLLEY HSP   CUMMINGS LLP
WA-SPOK   812429    Operations    NULL        CUMMINGS LLP

我正在尝试获得类似的结果

Name      ID        PGroup        Facilty     firmName
WA-SPOK   812429    Operations    SPOKANE     CUMMINGS LLP
WA-SPOK   812429    Operations    VLLEY HSP   CUMMINGS LLP

我试过分组。如何实现这一点。

标签： sqlsql-servergroup-byaggregate-functions

解决方案

这将受益于一些示例数据，基于这样的查询可以工作

with pgroup_cte([Name], ID, firmName, PGroup) as (
    select top(1) C.[name], C.id, TC.cname,
           (CASE WHEN CA.type_id = 231 and c.id = CA.id  then CA.value end)
    from tbCases C
         join tbCompanies TC on TC.company_id = C.lawfirm_id
         join tbAttributes CA on C.case_id = CA.case_id 
    where C.client_id = 9026
          and c.id = 812429 
          and (CASE WHEN CA.type_id IN ( 32, 38, 40, 52 ) and c.id = CA.id  
                    then CA.value end) is null)
select C.[name],
       C.id,
       pc.PGroup,
      (CASE WHEN  CA.type_id IN ( 32, 38, 40, 52 ) and c.id = CA.id  then CA.value end) as Facilty,
      TC.cname as firmName
from tbCases C
     join tbCompanies TC on TC.company_id = C.lawfirm_id
     join tbAttributes CA on C.case_id = CA.case_id 
     join pgroup_cte pc on c.[Name]=pc.[Name]
                           and c.id=pc.id
                           and tc.cname=pc.firmName
where C.client_id = 9026
      and c.id = 812429 
      and (CASE WHEN CA.type_id IN ( 32, 38, 40, 52 ) and c.id = CA.id  
                then CA.value end) is not null;

推荐阅读