MySQL -> 选择第二个最低值 - MySQL -> Select second lowest value

MySQL -> Select second lowest value

本文关键字：选择第二个最低值 MySQL | 更新日期: 2023-09-27

我目前正在使用min()来查找MYSQL数据库中的最低值。

如何找到第二低的值？

'select Date, Horse, ParentID, min(odds2) 
from horsesrp 
where odds2 < 3.1 and odds2 > 1.9 
and Date = "'.$id.'" 
AND Track IN ("Kempton (AW)","Wolverhampton (AW)") 
group by ParentID order by ParentID'

请注意，我仍然需要按父母ID排序，因为我想获得每个父母ID第二低的赔率2

所以我的数据库看起来像：

Racetime Odds
13:05     2
13:05     2.4
13:05     3
13:05     5
13:30     3
13:30     5
13:30     9
13:30     13.5
14:00     1.14
14:00     1.19
14:00     2
14:00     4

我想每次找到第二个最低值，但它必须在 1 到 2.9 之间

Racetime Odds
13:05     2.4
14:00     1.19

所以上面的输出将是

谢谢

艾玛

SELECT * FROM table_name ORDER BY id ASC LIMIT 1, 1

您可以通过多种方式获取第二个最低值。如果从此查询开始：

select Date, Horse, ParentID, min(odds2) 
from horsesrp 
where odds2 < 3.1 and odds2 > 1.9 and Date = "'.$id.'" AND
      Track IN ("Kempton (AW)","Wolverhampton (AW)") 
group by ParentID
order by ParentID;

那么最简单的方法是使用substring_index()/group_concat()技巧：

select Date, Horse, ParentID, min(odds2),
       substring_index(substring_index(group_concat(odds2) order by odds2, ',', 2), ',', -1) as second_odds
from horsesrp 
where odds2 < 3.1 and odds2 > 1.9 and Date = "'.$id.'" AND
      Track IN ('Kempton (AW)', 'Wolverhampton (AW)') 
group by ParentID
order by ParentID;

不过，我对这与你的样本数据有什么关系有点困惑。查询中没有racetime。

这是来自另一个问题的例子，

Product_id reg_price  sale_price
244         50          40
244         45          40 
244         45           0   
244         40           0

要找到第二低的sale_price，

SELECT
    MIN(NULLIF(sale_price, 0))
FROM `table`
WHERE product_id = 244;

编辑 1

所以在你的情况下，

SELECT
MIN(NULLIF(odds2,0))
FROM horserp
order by ParentID asc;

这也应该按父 ID 排序...

编辑 2 - 第二个查询

Select TOP 1 odds2 as '2nd lowest'
from (SELECT DISTINCT TOP 2 odds2 from horserp ORDER BY odds2 ASC)
a ORDER BY odds2 DESC

编辑 3 - 第二个查询的进一步嵌套

select *
from (Select TOP 1 odds2 as '2nd lowest'
from (SELECT DISTINCT TOP 2 odds2 from horserp ORDER BY odds2 ASC) a ORDER BY odds2 DESC)
order by ParentID desc;

查询

Select TOP 1 Salary as '2nd Lowest Salary'
from (SELECT DISTINCT TOP 2 Salary from Employee ORDER BY Salary ASC)
a ORDER BY Salary DESC

试试这个

    SELECT ParentID, Horse, Date, min(odds2) FROM
    (
            select DISTINCT HP.ParentID, HP.Horse, HP.Date, HP.odds2, SUB.min_odd from
            horsesrp HP INNER JOIN 
            (
              SELECT parentID,Date,Horse,min(odds2) min_odd FROM horsesrp GROUP BY parentID,parentID,Date,Horse
            ) SUB ON HP.ParentID = SUB.ParentID AND HP.Date = SUB.Date AND HP.Horse = SUB.Horse WHERE HP.odds2 < 3.1 and HP.odds2 > 1.9 
and HP.Date = "'.$id.'" 
AND HP.Track IN ("Kempton (AW)","Wolverhampton (AW)") AND HP.odds2>SUB.min_odd 
    ) SUB_END
    GROUP BY ParentID, Horse, Date
    ORDER BY ParentID

这里的问题是，由于您获得的是第二低的，因此如果您只有 1 行用于特定的 parentID，您将看不到最小值。

代码可能有错误，请告诉我。