Mysql Join 2表并选择日期范围之间的最大值和最小值 [英] Mysql Join 2 table and select maximum and minimum value between date range
本文介绍了Mysql Join 2表并选择日期范围之间的最大值和最小值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有2个表，订单和费率.我想加入这两个表，并在打开时间和关闭时间之间选择最大值和最小值
表1:订单
id type pair lot opentime openprice closeprice closetime
1 buy eurusd 0.01 2016-05-02 02:04:07 1.15112 1.14778 2016-05-02 03:05:00
2 sell gbpusd 0.01 2016-05-02 02:24:17 1.45221 1.44989 2016-05-02 03:05:00
表2:费率
id pair time price
10 eurusd 2016-05-02 03:00:00 1.14522
9 gbpusd 2016-05-02 03:00:00 1.44726
8 eurusd 2016-05-02 02:30:00 1.15258
7 gbpusd 2016-05-02 02:30:00 1.45311
6 eurusd 2016-05-02 02:00:00 1.15051
5 gbpusd 2016-05-02 02:00:00 1.45173
4 eurusd 2016-05-01 01:30:00 1.14258
3 gbpusd 2016-05-02 01:30:00 1.44326
2 eurusd 2016-05-02 01:00:00 1.15751
1 gbpusd 2016-05-02 01:00:00 1.45911
预期结果
id type pair lot opentime openprice closeprice closetime high timehigh low timelow
1 buy eurusd 0.01 2016-05-02 02:04:07 1.15112 1.14778 2016-05-02 03:05:00 1.15258 2016-05-02 02:30:00 1.14522 2016-05-02 03:00:00
2 sell gbpusd 0.01 2016-05-02 02:24:17 1.45221 1.44989 2016-05-02 03:05:00 1.45311 2016-05-02 02:30:00 1.44726 2016-05-02 03:00:00
我尝试此查询，但结果为空
SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime,high,timehigh,low,timelow FROM (SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime FROM `order` ORDER BY closetime DESC) table1
JOIN (SELECT MAX(price) as high,time as timehigh,pair as pairhigh FROM `rates` GROUP BY pair) table2 ON table1.pair=table2.pairhigh
JOIN (SELECT MIN(price) as low,time as timelow,pair as pairlow FROM `rates` GROUP BY pair) table3 ON table1.pair=table3.pairlow
WHERE table2.timehigh between table1.opentime and table1.closetime AND table3.timelow between table1.opentime and table1.closetime
不带where子句的尝试查询获取结果不为空，但不是预期的
SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime,high,timehigh,low,timelow FROM (SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime FROM `order` ORDER BY closetime DESC) table1
JOIN (SELECT MAX(price) as high,time as timehigh,pair as pairhigh FROM `rates` GROUP BY pair) table2 ON table1.pair=table2.pairhigh
JOIN (SELECT MIN(price) as low,time as timelow,pair as pairlow FROM `rates` GROUP BY pair) table3 ON table1.pair=table3.pairlow
结果
id type pair lot opentime openprice closeprice closetime high timehigh low timelow
1 buy eurusd 0.01 2016-05-02 02:14:07 1.15112 1.14778 2016-05-02 03:05:00 1.15751 2016-05-02 02:00:00 1.14258 2016-05-02 02:00:00
2 sell gbpusd 0.01 2016-05-02 03:24:17 1.45221 1.44989 2016-05-02 03:05:00 1.45911 2016-05-02 02:00:00 1.44326 2016-05-02 02:00:00
如何解决这个问题?
解决方案
我相信现在可以满足您的要求了吗?
SELECT *
FROM Orders
JOIN (SELECT price as maxPrice, pair, tr_time as maxTime FROM Rates
JOIN (SELECT Rates.pair, MAX(Rates.price) AS price
FROM Rates, Orders
WHERE (Rates.tr_time between Orders.opentime and Orders.closetime)
GROUP BY Rates.pair)
as MaxPrices USING (price, pair)) maxRates USING (pair)
JOIN (SELECT price AS minPrice, pair, tr_time as minTime FROM Rates
JOIN (SELECT Rates.pair, MIN(Rates.price) AS price
FROM Rates, Orders
WHERE (Rates.tr_time between Orders.opentime and Orders.closetime)
GROUP BY Rates.pair)
as minPrices USING (price, pair)) minRates USING (pair);
对于我来说，您的代码结构太差了，无法真正了解正在发生的事情，但是从本质上讲，您似乎并不知道诸如MAX()的聚合运算符仅返回单个值.
您还不清楚您想要的最低/最高价格(我假设每pair个价格)
运行一段代码，看看它是否返回正确?如果没有告诉我不匹配的地方，我可以开始修补它！
编辑 新结果:
i have 2 table, orders and rates. i want to join this two tables and select maximum and minimum value between opentime and closetime
Table 1 : orders
id type pair lot opentime openprice closeprice closetime
1 buy eurusd 0.01 2016-05-02 02:04:07 1.15112 1.14778 2016-05-02 03:05:00
2 sell gbpusd 0.01 2016-05-02 02:24:17 1.45221 1.44989 2016-05-02 03:05:00
Table 2 : rates
id pair time price
10 eurusd 2016-05-02 03:00:00 1.14522
9 gbpusd 2016-05-02 03:00:00 1.44726
8 eurusd 2016-05-02 02:30:00 1.15258
7 gbpusd 2016-05-02 02:30:00 1.45311
6 eurusd 2016-05-02 02:00:00 1.15051
5 gbpusd 2016-05-02 02:00:00 1.45173
4 eurusd 2016-05-01 01:30:00 1.14258
3 gbpusd 2016-05-02 01:30:00 1.44326
2 eurusd 2016-05-02 01:00:00 1.15751
1 gbpusd 2016-05-02 01:00:00 1.45911
expected results
id type pair lot opentime openprice closeprice closetime high timehigh low timelow
1 buy eurusd 0.01 2016-05-02 02:04:07 1.15112 1.14778 2016-05-02 03:05:00 1.15258 2016-05-02 02:30:00 1.14522 2016-05-02 03:00:00
2 sell gbpusd 0.01 2016-05-02 02:24:17 1.45221 1.44989 2016-05-02 03:05:00 1.45311 2016-05-02 02:30:00 1.44726 2016-05-02 03:00:00
i try this query but get empty result
SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime,high,timehigh,low,timelow FROM (SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime FROM `order` ORDER BY closetime DESC) table1
JOIN (SELECT MAX(price) as high,time as timehigh,pair as pairhigh FROM `rates` GROUP BY pair) table2 ON table1.pair=table2.pairhigh
JOIN (SELECT MIN(price) as low,time as timelow,pair as pairlow FROM `rates` GROUP BY pair) table3 ON table1.pair=table3.pairlow
WHERE table2.timehigh between table1.opentime and table1.closetime AND table3.timelow between table1.opentime and table1.closetime
try query without where clause get result not empty but not expected
SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime,high,timehigh,low,timelow FROM (SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime FROM `order` ORDER BY closetime DESC) table1
JOIN (SELECT MAX(price) as high,time as timehigh,pair as pairhigh FROM `rates` GROUP BY pair) table2 ON table1.pair=table2.pairhigh
JOIN (SELECT MIN(price) as low,time as timelow,pair as pairlow FROM `rates` GROUP BY pair) table3 ON table1.pair=table3.pairlow
Result
id type pair lot opentime openprice closeprice closetime high timehigh low timelow
1 buy eurusd 0.01 2016-05-02 02:14:07 1.15112 1.14778 2016-05-02 03:05:00 1.15751 2016-05-02 02:00:00 1.14258 2016-05-02 02:00:00
2 sell gbpusd 0.01 2016-05-02 03:24:17 1.45221 1.44989 2016-05-02 03:05:00 1.45911 2016-05-02 02:00:00 1.44326 2016-05-02 02:00:00
how to solve this?
解决方案
I believe this now matches your requirements?
SELECT *
FROM Orders
JOIN (SELECT price as maxPrice, pair, tr_time as maxTime FROM Rates
JOIN (SELECT Rates.pair, MAX(Rates.price) AS price
FROM Rates, Orders
WHERE (Rates.tr_time between Orders.opentime and Orders.closetime)
GROUP BY Rates.pair)
as MaxPrices USING (price, pair)) maxRates USING (pair)
JOIN (SELECT price AS minPrice, pair, tr_time as minTime FROM Rates
JOIN (SELECT Rates.pair, MIN(Rates.price) AS price
FROM Rates, Orders
WHERE (Rates.tr_time between Orders.opentime and Orders.closetime)
GROUP BY Rates.pair)
as minPrices USING (price, pair)) minRates USING (pair);
Your code structure is too poor for me to really work out what is going on, but essentially it seems that you were unaware that aggregate operators such as MAX() return only a single value.
You've also been unclear on what you wanted the minimum/maximum price of (I've assumed its per pair)
Have a run of the code and see if it returns right? If not tell me where it doesn't match and I can start patching it!
EDIT New results:
这篇关于Mysql Join 2表并选择日期范围之间的最大值和最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
