时间戳

首页 > 解决方案 > sql语句中的变量使查询挂起

variables - sql语句中的变量使查询挂起

问题描述

以下 sql 语句提供了它应该提供的内容。定义时间范围内的最小值。

select value, datetime from Schuppen 
where (value = (select min(value) from Schuppen 
    where (measure = 'temp') 
    and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00')) 
and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00';

当我使用变量而不是硬编码的日期时，语句会挂起。

set @startdate = cast('2018-11-01 00:00:00' as datetime);   
select value, datetime from Schuppen 
where (value = (select min(value) from Schuppen 
    where (measure = 'temp') 
    and datetime between @startdate and '2018-11-02 00:00:00')) 
and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00';

我想知道为什么这个语句不适用于我的 maria db 版本 10.1.26-MariaDB-0+deb9u1。

通过mysql控制台尝试。

mysql --user=XXXX --password=XXXX Outdoor-Air
...
Your MariaDB connection id is 194
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

...

MariaDB [Outdoor-Air]> set @startdate = cast('2018-11-01 00:00:00' as datetime);
Query OK, 0 rows affected (0.00 sec)
MariaDB [Outdoor-Air]> select @startdate;
+---------------------+
| @startdate          |
+---------------------+
| 2018-11-01 00:00:00 |
+---------------------+
1 row in set (0.01 sec)

MariaDB [Outdoor-Air]> select value, datetime from Schuppen where (value = (select min(value) from Schuppen where (measure = 'temp') and datetime between @startdate and '2018-11-02 00:00:00')) and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00';

现在声明挂起。

标签： variablesmariadb

解决方案

SELECT  `value`, `datetime`
    FROM  Schuppen
    WHERE  measure = 'temp'
      AND  `datetime` >= '2018-11-01'
      AND  `datetime` <  '2018-11-01' + INTERVAL 1 DAY
    ORDER BY `value`  ASC
    LIMIT  1;

这应该会有所帮助：

INDEX(measure, datetime, value)

此公式与您的公式之间的一个潜在区别：如果最低值在当天多次出现，则仅显示一行。

推荐阅读