如何对重复的对进行分组并编写查询？

Question

假设我有下表

Fruit  |   Price   |   Fruit   |   Price   |   State 
-----------------------------------------------------
apple  |     4     |   banana  |     3     |     NY
apple  |     6     |   banana  |     5     |     CA
apple  |     1     |   banana  |     2     |     PA
orange |     4     |   pear    |     5     |     FL
orange |     3     |   pear    |     4     |     OH

我想提出以下问题：

fruit1在所有州都比fruit2贵吗？

所以对所有对都要问这个问题。

1. 苹果()、苹果()、苹果()、香蕉()在所有州的价格都比香蕉高吗？
2. 橙在所有州都比梨贵吗？
3. 继续要求任何其他现有的对

因此，我假设我需要编写的查询需要以某种方式首先出现在多行中的组对。我该怎么做？

Answer 1

所以你会做：

select fruit1, fruit2
from t
group by fruit1, fruit2
having sum(price1 > price2) = count(*);

也就是说，进行一个聚合。计算fruit1价格大于fruit2价格的次数。如果这总是正确的，那么计数等于总计数。

如果数据都在一个表中：

select t1.fruit, t2.fruit
from t t1 join
     t t2
     on t1.state = t2.state and t1.fruit <> t2.fruit
group by t1.fruit, t2.fruit
having sum(t1.price > t2.price) = count(*);