SQL 语句中的多个 BETWEEN 和 LIMIT 运算符 - Multiple BETWEEN and LIMIT operators in SQL Statement

Multiple BETWEEN and LIMIT operators in SQL Statement

本文关键字：BETWEEN LIMIT 运算符语句 SQL | 更新日期: 2023-09-27

我正在尝试获取两行的结果集。

这两行是在 BETWEEN 运算符中指定的时间跨度之间找到的最后一行。

我怎样才能达到这个结果？

下面的代码不起作用。

SELECT 
DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5
AS october
FROM   solarenergy
WHERE ((DatumUhrzeit BETWEEN '2014-10-01 01:00:00' AND '2014-10-01 23:00:00') LIMIT 1)
   OR ((DatumUhrzeit BETWEEN '2014-10-02 01:00:00' AND '2014-10-02 23:00:00') LIMIT 1)
ORDER BY DatumUhrzeit DESC

您需要执行UNION才能应用两个单独的LIMIT，因为每个查询只能使用一个。要使用单个LIMIT子句，每个UNION组件都必须用()

(
  SELECT 
  DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5 AS october
  FROM solarenergy
  WHERE DatumUhrzeit BETWEEN '2014-10-01 01:00:00' AND '2014-10-01 23:00:00'
  ORDER BY DatumUhrzeit DESC
  LIMIT 1
) UNION (
  SELECT 
  DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5 AS october
  FROM solarenergy
  WHERE DatumUhrzeit BETWEEN '2014-10-02 01:00:00' AND '2014-10-02 23:00:00'
  ORDER BY DatumUhrzeit DESC
  LIMIT 1
)

如果要保证由UNION产生的两行订单，则必须在最后的()组之外应用

(
  SELECT 
  DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5 AS october
  FROM solarenergy
  WHERE DatumUhrzeit BETWEEN '2014-10-01 01:00:00' AND '2014-10-01 23:00:00'
  ORDER BY DatumUhrzeit DESC
  LIMIT 1
) UNION (
  SELECT 
  DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5 AS october
  FROM solarenergy
  WHERE DatumUhrzeit BETWEEN '2014-10-02 01:00:00' AND '2014-10-02 23:00:00'
  ORDER BY DatumUhrzeit DESC
  LIMIT 1
)
/* Global ORDER BY after UNION */
ORDER BY october ASC