如果存在，我需要为col1显示yes，如果不存在，则不需要为id显示yes，但在两种情况下都得到yes

Question

CREATE TABLE dim(
"ID"   NUMBER(38,0), 
"DATE" DATE, 
"Col1" VARCHAR2(50), 
"Col2" VARCHAR2(50)
);
INSERT INTO dim (id,col1,col2) VALUES (4160, 'yes', 'no');
INSERT INTO dim (id,col1,col2) VALUES (4160, 'no', 'no');
INSERT INTO dim (id,col1,col2) VALUES (4160, 'no', 'no');
INSERT INTO dim (id,col1,col2) VALUES (4160, 'no', 'no');

Select distinct
id,
case
when count('yes')over(partition by id,col1)>=1
then 'yes'
when count('no')over(partition by id,col1)>=1
and  count('yes')over(partition by id,col1)= 0
then 'no'
end as col1,
case
when count('yes')over(partition by id,col2)>=1
then 'yes'
when count('no')over(partition by id,col2)>=1
and  count('yes')over(partition by id,col2)= 0
then 'no'
end as col2
from   dim 

接收到的输出为:

col2对

这不是一个简单的MAX函数吗?

更多示例数据:

SQL> select * from dim order by id;
ID COL1       COL2
---------- ---------- ----------
1234 no         no          --> 1234: doesn't contain YES at all
4160 no         no          --> 4160: contains YES in COL1
4160 no         no
4160 yes        no
4160 no         no
5555 yes        yes         --> 5555: contains YES in both columns
9999 no         yes         --> 9999: contains YES in both columns
9999 yes        no
8 rows selected.

查询和结果:

SQL> select id, max(col1) col1, max(col2) col2
2  from dim
3  group by id
4  order by id;
ID COL1       COL2
---------- ---------- ----------
1234 no         no
4160 yes        no
5555 yes        yes
9999 yes        yes
SQL>

您可以将COUNT函数与条件聚合一起使用(当您无法排序数据以利用MIN或MAX等函数时，该函数将工作;例如，如果您的列中也有aaa和zzz数据)。

:

Select id,
CASE WHEN COUNT(CASE col1 WHEN 'yes' THEN 1 END) > 0 THEN 'yes' ELSE 'no' END
AS col1,
CASE WHEN COUNT(CASE col2 WHEN 'yes' THEN 1 END) > 0 THEN 'yes' ELSE 'no' END
AS col2
from   dim 
GROUP BY id

其中，对于样本数据输出:

<表类>IDCOL1COL2tbody><<tr>4160对没有