mysql - 找到每个组合出现的平均金额

Question

我有一张 table (table1):

| Country1 | Country2 |
  Canada      USA
  Canada      Mexico
  USA         Mexico
  USA         Canada
.
.
.
etc

然后我有另一个表(table2):

| Country | Date | Amount |
  Canada    01-01  1.00
  Canada    01-02  0.23
  USA       01-01  2.67
  USA       01-02  5.65
  USA       01-03  8.00
.
.
.
etc

我需要一个查询，它将两个表组合成如下所示:

| Country1 | Average_amount_when_combined_with_country2| Country2 | Average_amount_when_combined_with_country1 |
  Canada                       0.615                        USA                      4.16
  USA                          4.16                         Canada                   0.615

发生的事情是，当第一个表中的国家 1 与国家 2 出现时，我想在合并国家 2 时获得国家 1 的平均金额，然后反之亦然，当国家 1 合并时，国家 2 的平均值是组合的。我尝试了不同的连接技术，但不能完全发挥作用，但现在我认为我真的不能做任何传统的连接，我需要使用子查询的组合。我完全不知道如何仅在两个国家出现在同一日期时才获得平均值。这个查询是我能得到的最接近的，但问题是这只是获取整个国家的平均值，而不是两个国家合并时的平均值。

select country1, (select avg(amount) from table2 where country = country1) ,country2,(select avg(amount) from table2 where country = country2)
from table1

Answer 1

下面的 SELECT 应该可以解决您的问题，或者至少应该让您了解它是如何工作的。

SELECT  t1.Country1, 
        t1.Country2, 
        (
            SELECT  AVG(Amount) 
            FROM    table2 t2 
            WHERE   t2.Country = t1.Country1 AND 
                    EXISTS
                    (
                        SELECT 1      
                        FROM   table2 t3 
                        WHERE  t3.Country = t1.Country2 AND 
                               t2.Date = t3.Date
                    )
        ) Avg1, 
        (
            SELECT  AVG(Amount) 
            FROM    table2 t2 
            WHERE   t2.Country = t1.Country2 AND 
                    EXISTS
                    (
                        SELECT 1      
                        FROM   table2 t3 
                        WHERE  t3.Country = t1.Country1 AND 
                               t2.Date = t3.Date
                    )
        ) Avg2
FROM table1 t1

查看结果here .