如何在SQLite3中选择唯一的对(a,b)(b,a)作为一对并计算总频率?
例如
给定表1-
column1, column2, frequency
a b 2
b a 1
c d 1
c a 1
d c 1
生产
pair1, pair2, total_frequency
a b 3
c d 2
c a 1
你可以试试这个
select min(column1, column2), max(column1, column2), sum(frequency)
from table1
group by min(column1, column2), max(column1, column2)
order by min(column1, column2), max(column1, column2)