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

Question

我希望这有助于解释的不仅仅是标题。基本上我有一个查询：

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

上面的代码是错误的，因为我需要的是，如果用户至少存在 GROUP A 或 GROUP B 中的一个代码，那么只给出一个值1ELSENULL.

会产生的东西：

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

Answer 1

我确信有一种更聪明的方法，但如果我理解正确，这可能会解决问题：

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

Answer 2

我会将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;

Answer 3

这就是我会这样做的方式。

-- 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

Answer 4

您可以使用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;

Answer 5

我很感激你的反馈。我正在发布一个答案，因为它正在工作，但我不确定这是最好的答案：

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

我意识到这将使我获得按用户分组的结果，但这适用于我最后，我只想按用户计算案例。不确定是否有人可以使用它以获得更好的格式。