在mysql中使用where条件选择先前的id - selecting previous id with where condition in mysql

selecting previous id with where condition in mysql

本文关键字：选择 id 条件 where mysql | 更新日期: 2023-09-27

我使用以下查询来选择基于一些日期之间的前一只股票，但是，如果自动递增的id不正常（在删除某些记录时，它有间隙），则以下查询失败，

 SELECT in_stock, price 
 FROM $tablename  
 WHERE id=(SELECT (id-1) 
           FROM $tablename 
           WHERE sale_date BETWEEN '$from_date' AND '$to_date' 
           ORDER BY entered_date ASC 
           LIMIT 1)

帮助我选择上一个in_stock

如果ID不按顺序排列，您应该使用以下内容：

SELECT in_stock, price 
FROM $tablename  
WHERE entered_date=(
        SELECT MAX(entered_date) as max_date
        FROM   $tablename
        WHERE
          entered_date<(SELECT MIN(entered_date) as min_date
                        FROM $tablename 
                        WHERE
                          sale_date BETWEEN '$from_date' AND '$to_date')
      )

这很有效，

SELECT in_stock, price 
 FROM $tablename 
 WHERE id < (SELECT min(id) 
              FROM $tablename 
              WHERE sale_date BETWEEN '$from_date' AND '$to_date') ORDER BY id DESC LIMIT 1

试试这个

SELECT in_stock, price 
 FROM $tablename  
 WHERE id=(SELECT (SELECT MAX(id)
                    FROM $tablename
                    WHERE id < ( SELECT MAX(id)
                    FROM $tablename )) 
           FROM $tablename 
           WHERE sale_date BETWEEN '$from_date' AND '$to_date' 
           ORDER BY entered_date ASC 
           LIMIT 1)

SELECT in_stock,price
 FROM $tablename  
 WHERE id=(SELECT max(id) > 1
           FROM $tablename
           WHERE sale_date BETWEEN '$from_date' AND '$to_date' 
           ORDER BY entered_date ASC 
           LIMIT 1)