{'Guid': 'VARCHAR(64) NOT NULL', 'BaseCurrency': 'VARCHAR(8) NOT NULL', 'TargetCurrency': 'VARCHAR(8) NOT NULL', 'Rate': 'DECIMAL(18,8) DEFAULT 0', 'Package': 'VARCHAR(255)', 'Module': 'VARCHAR(255)', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'UpdateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'PRIMARY_KEY': 'BaseCurrency, TargetCurrency, Package'}
{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'BaseCurrency': 'VARCHAR(8) NOT NULL', 'TargetCurrency': 'VARCHAR(8) NOT NULL', 'Rate': 'DECIMAL(18,8) DEFAULT 0', 'Package': 'VARCHAR(255)', 'Module': 'VARCHAR(255)', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP'}
INSERT INTO data_currency
(Guid, BaseCurrency, TargetCurrency, Rate,
Package, Module)
VALUES
('{guid}', '{base_currency}',
'{target_currency}', {rate},
'{package}', 'ObjServiceCurrency')
ON DUPLICATE KEY UPDATE
Rate = VALUES(Rate),
Guid = VALUES(Guid)
INSERT INTO data_currency_history
(Guid, BaseCurrency, TargetCurrency, Rate,
Package, Module)
VALUES
('{guid}', '{base_currency}',
'{target_currency}', {rate},
'{package}', 'ObjServiceCurrency')
SELECT * FROM data_currency WHERE BaseCurrency = '{base_currency}'
AND Package = '{package}'
ORDER BY UpdateTime DESC LIMIT 1
SELECT Rate FROM data_currency WHERE BaseCurrency = '{base_currency}'
AND TargetCurrency = '{target_currency}'
AND Package = '{package}'
SELECT TargetCurrency, Rate, UpdateTime FROM data_currency WHERE BaseCurrency = '{base_currency}'
AND Package = '{package}'
ORDER BY TargetCurrency
SELECT TargetCurrency, Rate, CreateTime FROM data_currency_history WHERE BaseCurrency = '{base_currency}'
AND TargetCurrency = '{target_currency}'
AND Package = '{package}'
AND CreateTime BETWEEN '{start_time}' AND '{end_time}'
ORDER BY CreateTime
SELECT
DATE(CreateTime) AS ReadingDate,
TargetCurrency,
AVG(Rate) AS AvgRate,
MIN(Rate) AS MinRate,
MAX(Rate) AS MaxRate
FROM data_currency_history WHERE BaseCurrency = '{base_currency}'
AND Package = '{package}'
AND CreateTime BETWEEN '{start_time}' AND '{end_time}'
GROUP BY DATE(CreateTime), TargetCurrency ORDER BY ReadingDate, TargetCurrency