選擇兩個日期之間的第一行和最後一行 MySQL

我有疑問：

SELECT id, coins_id, first_coin, second_coin, price, `time`
FROM hist_all
WHERE `time` between (NOW() - interval 120 minute) AND NOW()
 AND (second_coin = 'USD' OR second_coin = 'USDT')
 AND first_coin = 'LSK';

查詢返回：

如何獲得相對於日期的第一行和最後一行？（在本例中，第一條記錄是id=1832, 最後一條id=14117）

在所有適當的時候，MySQL 中都會有公共表表達式（CTE）：

WITH T AS (
  SELECT id, coins_id, first_coin, second_coin, price, `time`
  FROM hist_all
  WHERE `time` between (NOW() - interval 120 minute) AND NOW()
    AND (second_coin = 'USD' OR second_coin = 'USDT')
    AND first_coin = 'LSK'
) 
(SELECT * FROM T ORDER BY time LIMIT 1)
UNION ALL
(SELECT * FROM T ORDER BY time DESC LIMIT 1);

如果您的 MySQL 版本不支持 CTE，您可以重複查詢：

 (SELECT id, coins_id, first_coin, second_coin, price, `time`
  FROM hist_all
  WHERE `time` between (NOW() - interval 120 minute) AND NOW()
    AND (second_coin = 'USD' OR second_coin = 'USDT')
    AND first_coin = 'LSK'
  ORDER BY TIME LIMIT 1)
  UNION ALL
 (SELECT id, coins_id, first_coin, second_coin, price, `time`
  FROM hist_all
  WHERE `time` between (NOW() - interval 120 minute) AND NOW()
    AND (second_coin = 'USD' OR second_coin = 'USDT')
    AND first_coin = 'LSK'
  ORDER BY time DESC LIMIT 1); 

引用自：https://dba.stackexchange.com/questions/204254