php - MySQL 和 PHP : Divide two numbers from two different tables with the same date

Question

我有这两个表:

表 1:TBL_USD_AUD

+----+------------+----------+
| id | date       | value    |
+----+------------+----------+
| 1  | 2015-04-24 | 1.278400 |
+----+------------+----------+
| 2  | 2015-04-23 | 1.287200 |
+----+------------+----------+
| 3  | 2015-04-22 | 1.288300 |
+----+------------+----------+
| 4  | 2015-04-21 | 1.291500 |
+----+------------+----------+
| 5  | 2015-04-20 | 1.294300 |
+----+------------+----------+

表 2:TBL_USD_EUR

+----+------------+----------+
| id | date       | value    |
+----+------------+----------+
| 1  | 2015-04-24 | 0.923873 |
+----+------------+----------+
| 2  | 2015-04-23 | 0.928333 |
+----+------------+----------+
| 3  | 2015-04-22 | 0.930839 |
+----+------------+----------+
| 4  | 2015-04-21 | 0.934579 |
+----+------------+----------+
| 5  | 2015-04-20 | 0.932575 |
+----+------------+----------+

如何选择两个表:

$x = "SELECT date,rate FROM `currency`.`tbl_usd_aud` WHERE `date` >= (NOW() - INTERVAL 40 DAY)";

和:

$f = "SELECT date,rate FROM `currency`.`tbl_usd_eur` WHERE `date` >= (NOW() - INTERVAL 40 DAY)";

然后我想以某种方式在同一日期获得 foreach 值，生成以下等式:

if ($x['date'] == $f['date']){
   echo "EUR-AUD: ".(1/$x['rate'])*$f['rate'];
}

Answer 1

你也可以在数据库中进行计算:

  SELECT a.date, a.rate x, e.rate f, e.rate/a.rate f_over_x
    FROM tbl_usd_aud a
    JOIN tbl_usd_eur e
      ON e.date = a.date
   WHERE a.date >= CURDATE() - INTERVAL 40 DAY

注意我更喜欢 CURDATE() 而不是 NOW() 因为它涉及较少的隐式转换而且 (1/x)*y = y/x