如何选择组中元素具有相同值的行

Question

我有一个这样的表：

prop1 | prop2
aaa   | xxx
aaa   | xxx
bbb   | yyy
bbb   | !yyy
ccc   | zzz
ccc   | zzz
ddd   | !kkk
ddd   | kkk

我想成为：

prop1 | prop2
bbb   | yyy
bbb   | !yyy
ddd   | !kkk
ddd   | kkk

在SQL(firebird(中我该怎么做？

Answer 1

如果您希望从表中选择的结果看起来像您的示例中所写的那样，这可能是查询：

select * from t t1 where exists
(select * from t t2
where t1.prop1 = t2.prop1 and t1.prop2 <> t2.prop2)

如果您想要(正如主题所说(选择所有行都具有相同值的组，那么查询如下所示：

select prop1, MAX(prop2) from t
group by prop1 having MAX(prop2) = MIN(prop2)

如果你想让查询后的表看起来像你的例子(即删除你从上面的查询中得到的组(，那么它就是

execute block as
declare p varchar(100);
begin
for select prop1 from t
group by prop1 having MAX(prop2) = MIN(prop2)
into :p do
begin
delete from t where prop1 = p;
end
end