mysql - 如何对货币汇率表进行正确的 SQL 查询？

Question

我有两个表，通过 FOREIGN KEY 连接

货币表：

CREATE TABLE Currencies
            (id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
            mark varchar(3) NOT NULL,
            english_name varchar(50) NOT NULL,
            deutsch_name varchar(50) NOT NULL,
            russian_name varchar(50) NOT NULL,
            chinese_name varchar(50) NOT NULL);

在货币表中，标记保留代表货币的符号，例如“美元”。

CoreRates 表：

CREATE TABLE CoreRates
            (id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
            datetime datetime NOT NULL,
            base_currency_id int NOT NULL,
            target_currency_id int NOT NULL,
            rate float(40,20) NOT NULL,
            CONSTRAINT rate_info UNIQUE(datetime,base_currency_id,target_currency_id),
            FOREIGN KEY (base_currency_id) REFERENCES Currencies(id),
            FOREIGN KEY (target_currency_id) REFERENCES Currencies(id));

base_currency_id 始终等于货币的 id，标记 = "EUR"。

我需要在选定的时期（两个给出日期时间字符串，例如“2020-07-25”、“2020-07-28”）。我需要将这些数据放入 DataMarts3 表中：

CREATE TABLE DataMart3
            (datetime datetime NOT NULL,
            base_mark varchar(3) NOT NULL,
            target_mark varchar(3) NOT NULL,
            rate float(40,20) NOT NULL)

什么是正确的 SQL 查询？

我试过这个：

INSERT INTO DataMart3 (
            DataMart3.datetime,
            DataMart3.base_mark,
            DataMart3.target_mark,
            DataMart3.rate
        )
SELECT CoreRates.datetime,
               'USD',
               'RUB',
               (SELECT (1 / CoreRates.rate) FROM CoreRates WHERE CoreRates.target_currency_id = (SELECT Currencies.id FROM Currencies WHERE Currencies.mark = 'USD') AND CoreRates.base_currency_id = (SELECT Currencies.id FROM Currencies WHERE Currencies.mark = 'EUR'))
               * 
               (SELECT (CoreRates.rate) FROM CoreRates WHERE CoreRates.base_currency_id = (SELECT Currencies.id FROM Currencies WHERE Currencies.mark = 'EUR') AND CoreRates.target_currency_id = (SELECT Currencies.id FROM Currencies WHERE Currencies.mark = 'CNY'))
FROM CoreRates
WHERE '2021-06-25' <= CoreRates.datetime AND CoreRates.datetime <= '2021-08-25'

但是我正在使用的 MySQL 说“子查询返回超过 1 行”

更新：表的一些种子数据：对于货币表：

INSERT INTO `Currencies` (`id`, `mark`, `english_name`, `deutsch_name`, `russian_name`, `chinese_name`) VALUES
(1, 'USD', 'Dollar', 'Dollar', 'Доллар', '美元'),
(2, 'EUR', 'Euro', 'Euro', 'Евро', '歐元'),
(3, 'RUB', 'Ruble', 'Rubel', 'Рубль', '盧布'),
(4, 'CNY', 'Yuan', 'Yuan', 'Юань', '元');

对于 CoreRates 表：

INSERT INTO `CoreRates` (`id`, `datetime`, `base_currency_id`, `target_currency_id`, `rate`) VALUES
(86, '2021-07-25 22:55:04', 2, 1, 1.1775000095),
(87, '2021-07-25 22:55:04', 2, 2, 1.0000000000),
(88, '2021-07-25 22:55:04', 2, 3, 86.8230056763),
(89, '2021-07-25 22:55:04', 2, 4, 7.6318497658),
(93, '2021-07-25 23:50:04', 2, 1, 1.1775000095),
(94, '2021-07-25 23:50:04', 2, 2, 1.0000000000),
(95, '2021-07-25 23:50:04', 2, 3, 86.8230056763),
(96, '2021-07-25 23:50:04', 2, 4, 7.6318449974),
(100, '2021-07-26 00:45:04', 2, 4, 7.6292119026),
(101, '2021-07-26 00:45:04', 2, 2, 1.0000000000),
(102, '2021-07-26 00:45:04', 2, 3, 86.7930831909),
(103, '2021-07-26 00:45:04', 2, 1, 1.1770939827),
(107, '2021-07-26 09:00:04', 2, 4, 7.6418271065),
(108, '2021-07-26 09:00:04', 2, 2, 1.0000000000),
(109, '2021-07-26 09:00:04', 2, 3, 87.1985549927),
(110, '2021-07-26 09:00:04', 2, 1, 1.1783670187),
(114, '2021-07-26 09:55:03', 2, 4, 7.6384859085),
(115, '2021-07-26 09:55:03', 2, 2, 1.0000000000),
(116, '2021-07-26 09:55:03', 2, 3, 87.2046127319),
(117, '2021-07-26 09:55:03', 2, 1, 1.1774890423);

标签： mysqlsql