Mysql - 在同一个表中为另一个查询划分 1 个查询

Question

包含货币、汇率、临时时间的表格 - 我想在相同的临时时间内为美元汇率划分一些选定的汇率。 .

CREATE TABLE IF NOT EXISTS `currency_rates` (
   `id` INT NOT NULL AUTO_INCREMENT
 , `currency` VARCHAR(254) NOT NULL
 , `rate` FLOAT NOT NULL
 , `temptime` DATE NOT NULL
 , PRIMARY KEY (`id`)
) ENGINE = MyISAM;


INSERT INTO `currency_rates` 
(currency, rate, temptime)
VALUES 
('USD', '1.232', '2018-03-16'),
('AUD', '133.82', '2018-03-16'),
('CAD', '1.99', '2018-03-16'),
('EUR', '1.6654', '2018-03-16'),
('USD', '1.242', '2018-03-17'),
('AUD', '132.82', '2018-03-17'),
('CAD', '1.79', '2018-03-17'),
('EUR', '1.4654', '2018-03-17'),
('USD', '1.272', '2018-03-19'),
('AUD', '123.82', '2018-03-19'),
('CAD', '1.765', '2018-03-19'),
('EUR', '1.254', '2018-03-19');


$result1 = mysql_query("SELECT currency, rate, temptime  FROM `currency_rates` WHERE currency = 'USD' ORDER BY temptime asc"); 

$result2 = mysql_query("SELECT currency, date, rate FROM `currency_rates` WHERE currency IN ('AUD', 'CAD', 'EUR') ORDER BY temptime asc"); 

$result3 = I want that every rate from $result2 is divided for the USD rate from $result1 with the same temptime. 

谢谢

Answer 1

您可以使用交叉连接 USD 查询

$result2 = mysql_query("SELECT a.currency, a.date, a.rate/b.rate
          FROM `currency_rates` a 
          CROSS JOIN  `currency_rates` b 
          WHERE b.currency = 'USD'
          AND  a.currency IN ('AUD', 'CAD', 'EUR') 
          ORDER BY a.temptime asc"); 

如果您需要每个临时时间的结果(如 RaymondNijland 所建议)

$result2 = mysql_query("SELECT a.currency, a.date, a.rate/b.rate
          FROM `currency_rates` a 
          INNER JOIN  `currency_rates` b  ON  a.temptime = b.temptime
          AND b.currency = 'USD'
          AND a.currency IN ('AUD', 'CAD', 'EUR') 
          ORDER BY a.temptime asc");