mysql select在以前的版本上不起作用

dba5bblo 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(357)

我有以下几点 select 它将查找特定日期范围内发生的最高、最低、平均温度和日期时间。它在10.3.22-mariadb上运行良好，但在10.0.38-mariadb上返回错误。它看起来不支持windows功能。有没有可能帮我 select 10.0.38行吗？
先谢谢你

SELECT DISTINCT max(Temperature) OVER () as maxtemp,        
                min(Temperature) OVER () as mintemp, 
                avg(Temperature) OVER () as avtemp, 
                FIRST_VALUE(DateTimeValue) OVER (ORDER BY Temperature ASC) as dt_at_min,             
                FIRST_VALUE(DateTimeValue) OVER (ORDER BY Temperature DESC) as dt_at_max 
FROM TempHistory 
        WHERE date(DateTimeValue) = '2020-07-08'AND 
        SensorPosition = 'Base' and 
        Location = 'Home'

table

Temperature    DateTimeValue         WarnCrit     Location    SensorPosition
29.1        2020-06-22 10:08:30         0          Home           Base
29.2        2020-06-22 09:38:28         0          Home           Base
29.2        2020-06-22 09:08:26         0          Home           Base
28.9        2020-06-22 08:38:26         0          Home 2         1stFloor
28.7        2020-06-22 08:08:24         0          Home           Base
28.7        2020-06-22 07:38:22         0          Home 2         1stFloor
29.2        2020-06-22 07:08:21         0          Home           Base
29.8        2020-06-22 06:38:20         0          Store          Entrance
29.9        2020-06-22 06:08:18         0          Store          Entrance

错误（我没有包括所有的错误，但是如果需要的话，我会的）

Error
Static analysis:

42 errors were found during analysis.

An alias was previously found. (near "maxtemp" at position 44)
An alias was expected. (near " " at position 43)
Unexpected token. (near "maxtemp" at position 44)
Unexpected token. (near "," at position 51)
Unrecognized keyword. (near "min" at position 78)
Unexpected token. (near "(" at position 81)
Unexpected token. (near "Temperature" at position 82)
Unexpected token. (near ")" at position 93)
Unexpected token. (near "OVER" at position 95)
Unexpected token. (near "(" at position 100)
Unexpected token. (near ")" at position 101)
Unrecognized keyword. (near "as" at position 103)
Unexpected token. (near "mintemp" at position 106)
Unexpected token. (near "," at position 113)

10.3.22的结果

maxtemp    mintemp    avtemp    dt_at_min              dt_at_max
30          28.6      29.668    2020-07-08 07:00:18    2020-07-08 9:00:23

sql mysql mariadb

来源：https://stackoverflow.com/questions/62788616/mysql-select-doesnt-work-on-previous-versions

1条答案

按热度按时间

jv4diomz1#

mariadb直到版本10.2才开始支持窗口函数，因此您需要使用子查询重写查询以获得最小/最大/平均温度，然后将其与原始表连接两次以获得最小/最大日期值。注意：我只包含了您在问题中提供的数据，但您应该能够将其扩展到完整的表中：

SELECT MIN(mintemp) AS mintemp,
       MAX(maxtemp) AS maxtemp,
       AVG(avtemp) AS avtemp,
       MIN(t1.DateTimeValue) AS dt_at_min,
       MAX(t2.DateTimeValue) AS dt_at_max
FROM (
  SELECT MIN(Temperature) AS mintemp,
         MAX(Temperature) AS maxtemp,
         AVG(Temperature) AS avtemp
  FROM TempHistory
  WHERE DATE(DateTimeValue) = '2020-06-22' AND 
        SensorPosition = 'Base' AND 
        Location = 'Home'
) agg
JOIN (
  SELECT *
  FROM TempHistory
  WHERE DATE(DateTimeValue) = '2020-06-22' AND 
        SensorPosition = 'Base' AND 
        Location = 'Home'
) t1 ON t1.Temperature = agg.mintemp
JOIN (
  SELECT *
  FROM TempHistory
  WHERE DATE(DateTimeValue) = '2020-06-22' AND 
        SensorPosition = 'Base' AND 
        Location = 'Home'
) t2 ON t2.Temperature = agg.maxtemp

输出（对于示例数据）：

mintemp     maxtemp     avtemp      dt_at_min               dt_at_max
28.7        29.2        29.08000    2020-06-22 08:08:24     2020-06-22 09:38:28

在dbfiddle上演示

赞(0）回复(0）举报 2021-07-24

我来回答

mysql select在以前的版本上不起作用

1条答案

相关问题

热门标签

最新问答