mysql - 计算国家同时出现时的组合数

Question

我有一个这样的表:

Location 1 | Location 2 | ID (autoIncremented)

位置行在此语法中:

Country*State*City

所以我可以有这样的行:

USA*NY*BROOKLYN
USA*WASHINGTON*SEATTLE
USA*WASHINGTOM*BELLINGHAM
CANADA*BC*VANCOUVER
CANADA*MANITOBA*WINNIPEG
MEXICO*MEHICO*MEXICOCITY

我想得到这样的结果:

Country 1 | Country 2 | count([count of all the occurrences together])

但我坚持要实现这一点。我想计算一起出现的国家的组合。我需要提取的国家部分，所以我使用:

substring_index(location1, '*', 1) as country

我拥有的最接近的完整查询是:

select 
substring_index(location1, '*', 1) as country1,
substring_index(location2, '*', 1) as country2
count(*)
FROM location_table
GROUP BY [not sure which to group by]

Answer 1

下面是其中一个选项。这不是有效的 SQL(因为我在 GROUP BY 中使用了别名)——只是我的想法的一个例子。您将需要使用 substring 表达式作为使用非规范化数据的惩罚。

GROUP BY ( LEAST(country1, country2) + GREATEST(country1, country2) )

上面假设 USA |加拿大 和加拿大 |美国应该一起算。