mysql - 获取另一个mysql的列和值的平均值

Question

我不确定我使用的标题是否正确，但让我解释一下发生了什么。我有一张汇率表，每天更新。这是表格:

DateCreated |  Bank  |  Buy
2013-10-09  |  tc    |  2.40
2013-10-09  |  bank1 |  2.45
2013-10-09  |  bank2 |  2.46
2013-10-09  |  bank3 |  2.45

所以我需要的是与“tc”银行的平均比较，如下所示:

Date        | tc    |  banks
2013-10-09  | 2.40  |  2.453   <- this is the average of all three banks for that date

希望这是有道理的，这是我尝试过的:

SELECT e.datecreated, e.buy, (SELECT AVG(buy) FROM exchange WHERE idbank <>  'tc'
AND datecreated = e.datecreated) AS banks
FROM exchange e
WHERE idbank =  'tc'
ORDER BY e.datecreated ASC

但是这是转储重复项，并且看起来像是错误的数据..帮助？

Answer 1

这是获取指定结果的一种方法:

SELECT e.datecreated
     , MAX(IF(e.idbank='tc',e.buy,NULL)) AS tc
     , AVG(IF(e.idbank='tc',NULL,e.buy)) AS banks
  FROM exchange e
 GROUP BY e.datecreated

还有许多其他查询将返回等效结果。