MySQL中数据库的最小值 [英] Min value from Database in MySQL

问题描述

我正在尝试查找过去30天的最小值，在我的表格中，每天都有一个条目正在使用此查询

Am trying to find the min value from past 30 days, in my table there is one entry for every day, am using this query

SELECT MIN(low), date, low 
FROM historical_data 
WHERE name = 'bitcoin' 
ORDER BY STR_TO_DATE(date,'%d-%m-%Y') DESC 
LIMIT 7

但是该值不能重现正确的值.我的桌子的结构是

But this value not returing the correct value. The structure of my table is

表结构

存储的表数据是这样的

表数据样式

现在我需要的是获得最小的低价值.但是我的查询不起作用，它给了我错误的值，即使在表中也不存在.

Now what i need is to get the minimum low value. But my query not working it give me wrong value which even did not exist in table as well.

更新:

这是我更新的表结构. 在此处输入图片描述

Here is my updated Table Structure. enter image description here

这是我在此表中的数据，看起来像这样 在此处输入图片描述

And here is my data in this table which look like this enter image description here

现在，如果您查看数据，我想检查令牌omisego的名称，并记录过去7天的低值，即从2017-12-25到2017-12-19 在此强制转换中，低值是9.67，但是我当前的查询和我的某些成员建议的查询未带来正确的答案.

Now if you look at the data, i want to check the name of token omisego and fatch the low value from past 7 days which will be from 2017-12-25 to 2017-12-19 and in this cast the low value is 9.67, but my current query and the query suggested by my some member did not brings the right answer.

更新2:

http://rextester.com/TDBSV28042

在这里，基本上我有1400 coins和token历史数据，这意味着在同一日期，如2017-12-25，但我将有1400多个条目，但名称不同，总共我有然后是650000记录.因此每个日期都有许多名称不同的条目.

Here it is, basically i have more then 1400 coins and token historical data, which means that there will me more then 1400 entries for same date like 2017-12-25 but having different name, total i have more then 650000 records. so every date have many entries with different names.

推荐答案

要获得每个组的最低行，可以使用以下命令

To get the lowest row per group you could use following

SELECT a.*
FROM historical_data a 
LEFT JOIN historical_data b ON a.name = b.name 
AND a.low > b.low
WHERE b.name IS NULL
AND DATE(a.date) >= '2017-12-19' AND DATE(a.date) <= '2017-12-25' 
AND a.name = 'omisego'

或

SELECT a.*
FROM historical_data a 
JOIN (
    SELECT name,MIN(low) low
    FROM historical_data
    GROUP BY name
) b USING(name,low)
WHERE  DATE(a.date) >= '2017-12-19' AND DATE(a.date) <= '2017-12-25' 
AND a.name = 'omisego'

> 演示

对于7天或n天的最后30天，您可以将上述查询写为

For last 30 day of 7 days or n days you could write above query as

SELECT a.*, DATE(a.`date`)
FROM historical_data2 a 
LEFT JOIN historical_data2 b ON a.name = b.name 
AND DATE(b.`date`) >= CURRENT_DATE() - INTERVAL 30 DAY 
AND DATE(b.`date`) <= CURRENT_DATE()
AND a.low > b.low
WHERE b.name IS NULL
AND a.name = 'omisego'
AND DATE(a.`date`) >= CURRENT_DATE() - INTERVAL 30 DAY
AND DATE(a.`date`) <= CURRENT_DATE()
;

演示

但是请注意，它可能会返回多个低值相同的记录，要在其中选择1行，并为不同的属性指定另一个条件

But note it may return more than one records where low value is same, to choose 1 row among these you have specify another criteria to on different attribute

这篇关于MySQL中数据库的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！