mysql - 如何更改此查询，以便无论数据库中存在什么数据它都可以工作

Question

任何时候我只需要从数据库中获取 5 条记录，这是我的查询

这是我的数据

CREATE TABLE IF NOT EXISTS `historical_data` (
  `symbol_name` varchar(70) DEFAULT NULL,
  `current_day` date DEFAULT NULL,
  `open_val` decimal(15,2) DEFAULT NULL,
  `high_val` decimal(15,2) DEFAULT NULL,
  `low_val` decimal(15,2) DEFAULT NULL,
  `close_val` decimal(15,2) DEFAULT NULL,
  `last_val` decimal(15,2) DEFAULT NULL,
  `prevclose_val` decimal(15,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;




INSERT INTO `historical_data` (`symbol_name`, `current_day`, `open_val`, `high_val`, `low_val`, `close_val`, `last_val`, `prevclose_val`) VALUES
        ('TATAELXSI', '2015-12-01', 2036.00, 2058.00, 1983.00, 2004.95, 2002.55, 2031.70),
        ('TATAELXSI', '2015-12-02', 2002.00, 2012.00, 1961.30, 1970.15, 1969.00, 2004.95),
        ('TATAELXSI', '2015-12-03', 1968.00, 2055.00, 1921.50, 1996.80, 1991.00, 1970.15),
        ('TATAELXSI', '2015-12-04', 1977.90, 2035.00, 1961.00, 1976.15, 1982.00, 1996.80),
        ('TATAELXSI', '2015-12-07', 1991.00, 2022.55, 1971.10, 1979.80, 1981.00, 1976.15),
        ('TATAELXSI', '2015-12-08', 1972.00, 2015.00, 1941.10, 1956.10, 1952.00, 1979.80),
        ('TATAELXSI', '2015-12-09', 1950.00, 1979.90, 1822.65, 1848.00, 1867.40, 1956.10),
        ('TATAELXSI', '2015-12-10', 1867.00, 1955.00, 1836.00, 1939.00, 1954.60, 1848.00),
        ('TATAELXSI', '2015-12-30', 2264.00, 2292.00, 2235.00, 2243.00, 2247.00, 2257.20),
        ('TATAELXSI', '2015-12-29', 2251.00, 2298.00, 2226.20, 2257.20, 2253.00, 2246.55),
        ('TATAELXSI', '2015-12-28', 2274.00, 2280.85, 2232.00, 2246.55, 2243.00, 2267.85),
        ('TATAELXSI', '2016-01-01', 2252.00, 2281.20, 2223.50, 2250.75, 2245.00, 2244.05),
        ('TATAELXSI', '2016-01-04', 2243.00, 2281.00, 2185.00, 2211.95, 2211.90, 2250.75),
        ('TATAELXSI', '2016-01-05', 2222.70, 2238.00, 2170.00, 2180.05, 2172.00, 2211.95),
        ('TATAELXSI', '2016-01-06', 2180.70, 2242.50, 2155.00, 2168.25, 2172.00, 2180.05),
        ('TATAELXSI', '2016-01-07', 2134.30, 2145.00, 2075.00, 2083.50, 2080.00, 2168.25),
        ('TATAELXSI', '2016-01-08', 2103.00, 2124.00, 2039.00, 2052.25, 2040.00, 2083.50)

还有我的 sqlfiddle

http://sqlfiddle.com/#!9/3f9c1b/2

SELECT * FROM historical_data WHERE symbol_name= 'TATAELXSI' and  current_day BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY) AND NOW() order by current_day asc

如果有股票记录(意味着市场没有假期)，上面的查询会很好地工作，它会获取 5 条记录，以防万一是星期一(因为没有周六和周日的数据)

我需要使用(将间隔时间从 5 天更改为 7 天)

SELECT * FROM historical_data WHERE symbol_name= 'TATAELXSI' and  current_day BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW() order by current_day asc

我的问题是，无论数据库中存在什么数据，是否都可以使其工作

Answer 1

如果我理解正确的话，您需要最近 5 天的数据，按升序排序。您可以使用子查询和LIMIT来完成此操作:

SELECT hd.*
FROM (SELECT hd.*
      FROM historical_data hd
      WHERE symbol_name = 'TATAELXSI' and 
            current_day BETWEEN DATE_SUB(NOW(), INTERVAL 10 DAY) AND NOW()
      ORDER BY current_day DESC
      LIMIT 5
     ) hd
ORDER BY current_day ASC;

严格来说，WHERE 子句中不需要对 current_day 进行比较。但是，这限制了正在考虑的数据量，这应该有利于性能。