sql - sqlite3选择同时引用另一列的列

我在my_table中有看起来像的数据

fruit1     fruit2      state
apple      orange      iowa
apple      orange      delaware
apple      orange      florida
grape      plum        texas
kiwi       orange      hawaii
kiwi       orange      alabama

我想选择水果1和2配对的次数。预期的结果是

fruit1     fruit2      times_paired
apple      orange      3
grape      plum        1
kiwi       orange      2

我不确定如何在sql中查找对。我知道那会是

select fruit1, fruit2, count(*) from my_table order by count(*)

最佳答案

您必须group by对。

select fruit1,fruit2,count(*)
from my_table
group by fruit1,fruit2

如果需要像a,b这样的对与b,a相同进行计数，请使用least和greatest。

select least(fruit1,fruit2),greatest(fruit1,fruit2),count(*)
from my_table
group by least(fruit1,fruit2),greatest(fruit1,fruit2)