mysql时间段的的最大最小值_Mysql Join 2表并选择日期范围之间的最大值和最小值...

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: