sql语句中的变量使查询挂起 [英] Variables in sql statement make query hang
本文介绍了sql语句中的变量使查询挂起的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下sql语句可提供应有的功能.在定义的时间范围内的最小值.
the following sql statement delivers what it should. The min value in a defined timeframe.
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';
当我使用变量而不是硬编码日期时,该语句将挂起.
When I use a variable, instead of the hardcoded dates, the statement hangs.
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.
I wonder why this statement does not work against my maria db version 10.1.26-MariaDB-0+deb9u1.
通过mysql控制台尝试.
Tried via the mysql console.
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';
现在语句挂起.
推荐答案
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)
此公式与您的公式之间可能存在的差异:如果最低值在当天发生多次,则只会显示一行.
One potential difference between this formulation and yours: This will show only one row if the lowest value occurs more than once on that day.
这篇关于sql语句中的变量使查询挂起的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文