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

CASE语句中使用的IN运算符的值应计数一次

sql-server sql

kazzi · 技术社区 · 6 年前

我希望这有助于解释更多的标题。基本上我有一个疑问:

SELECT 
  e.USER
, e.CODE
, CASE WHEN 
  e.CODE IN ('a.01', 'b.04', 'c.20', x.01)
  THEN 1
  ELSE NULL
  END AS case
FROM TABLE e

这会让我:

USER   CODE     case
01     a.01     1
01     a.02     NULL
01     a.04     NULL
01     b.04     1
01     c.20     1
02     a.11     NULL
02     b.04     1
03     b.05     NULL
04     c.20     1

不过,我需要的是更复杂一点。我需要对代码进行分组,以便为每个用户每组提供一个值 USER :

例如:

, CASE WHEN 
  e.CODE IN ('a.01', 'b.04') --Group A
  OR
  e.CODE IN ('c.20', x.01)   --Group B
  THEN 1
  ELSE NULL
  END AS case

1 其他 NULL .

USER   CODE     case
01     a.01     1    --Group A
01     a.02     NULL
01     a.04     NULL
01     b.04     NULL --GROUP A, now NULL because we just need to count once per group 
01     c.20     1    --GROUP B
02     a.11     NULL
02     b.04     1   
03     b.05     NULL
04     c.20     1

如果这有帮助,我最终希望计算每个用户的案例,以生成以下内容:

USER     count
01       2       --Not 3
02       1
03       1
04       1

5 回复 | 直到 6 年前

Error_2646 6 年前

我相信有一种更聪明的方法,但如果我理解正确的话,这可能会起到作用:

SELECT sub.user,
       sub.code,
       CASE WHEN ROW_NUMBER() OVER
                 ( PARTITION BY GroupCode
                       ORDER BY inGroup DESC
                 ) = 1 
             AND MAX(inGroup) OVER
                 ( PARTITION BY GroupCode
                 ) = 1 THEN 1 
            ELSE 0
        END FieldYourAskingFor       
  FROM (SELECT e.*,
               CASE WHEN e.CODE IN ('a.01', 'b.04', 'c.20', x.01) THEN 1 
                END AS inGroup,
               CASE WHEN e.CODE IN ('a.01', 'b.04') THEN 'GroupA'
                    WHEN e.CODE IN ('c.20', x.01) THEN 'GroupB'
                END AS GroupCode
          FROM TABLE e
       ) sub

Yogesh Sharma 6 年前

我会用 row_number() case 表达式:

select USER, CODE, 
       (case when row_number() over (partition by USER, grp order by CODE) = 1 and
                  grp is not null 
             then 1
        end) as case
from ( select *, (case when CODE IN ('a.01', 'b.04') then 'GA' 
                       when CODE IN ('c.20', 'x.01') then 'GB' 
                  end) as grp
       from table
    ) t;

CrimsonKing 6 年前

我就是这样做的。

-- create test table and data
declare @tbl table(usr char(2), code char(4))
insert into @tbl values ('01', 'a.01'), ('01', 'b.04')
insert into @tbl values ('01', 'c.20'), ('02', 'b.04'), ('04', 'c.20')

-- actual query
select
   usr,
   sign(sum(iif(code in ('a.01', 'b.04'), 1, 0))) +          -- 1 if group a, else 0  
   sign(sum(iif(code in ('c.20', 'x.01'), 1, 0))) as count   -- 1 if group b, else 0
from @tbl
group by usr

usr count
--- -----
01      2
02      1
04      1

Gordon Linoff 6 年前

你可以用 ROW_NUMBER()

SELECT e.USER, e.CODE,
       (CASE WHEN ROW_NUMBER() OVER (PARTITION BY flag, e.USER ORDER BY e.code) = 1
             THEN flag
        END) as flag
FROM (SELECT e.*,
             (CASE WHEN e.CODE IN ('a.01', 'b.04', 'c.20', x.01) THEN 1 END) as flag
      FROM TABLE e
     ) e;

编辑:

SELECT e.USER, e.CODE,
       (CASE WHEN ROW_NUMBER() OVER (PARTITION BY flag, e.USER ORDER BY e.code) = 1
             THEN SIGN(flag)
        END) as flag
FROM (SELECT e.*,
             (CASE WHEN e.CODE IN ('a.01', 'b.04') THEN 1
                   WHEN e.CODE IN ('c.20', x.01) THEN 2
              END) as flag
      FROM TABLE e
     ) e;

kazzi 6 年前

我很感激你的反馈。我正在发布一个有效的答案,但我不确定这是否是最好的答案:

SELECT
   t.USER
  ,t.CODE
  ,COUNT DISTINCT(t.case)

FROM (

SELECT 
  e.USER
, e.CODE
, CASE 
  WHEN e.CODE IN ('a.01', 'b.04') --Group A
  THEN 'grpA'
  WHEN e.CODE IN ('c.20', x.01)   --Group B
  THEN 'grpB'
  ELSE NULL
  END AS case

FROM TABLE e
) t

GROUP BY t.USER