时间戳

首页 > 解决方案 > Comparison operator is not working in MySQL query

mysql - Comparison operator is not working in MySQL query

问题描述

i have this MySQL query:

SELECT * FROM forex_pair_signals AS SENALES 
JOIN forex_pair_price AS PRECIO 
ON SENALES.forex_pair_price_id = PRECIO.forex_pair_price_id 
JOIN forex_pair AS PARES 
ON PRECIO.forex_pair_id = PARES.forex_pair_id
WHERE 
PRECIO.forex_pair_price_time >= '2019-02-01 00:00' AND
PRECIO.forex_pair_price_time <= '2019-02-07 01:00' AND 
TIME (PRECIO.forex_pair_price_time) BETWEEN '06:00' AND '13:00' AND 
PARES.forex_pair_id = 13 AND SENALES.forex_pair_signals_result = 1 AND
PRECIO.forex_pair_price_rsi >= '80.00' 
OR PRECIO.forex_pair_price_rsi <= '20.00' 
ORDER BY SENALES.forex_pair_signals_id DESC

This query works fine except when add the following filter:

AND PRECIO.forex_pair_price_rsi >= '80.00' OR 
PRECIO.forex_pair_price_rsi <= '20.00'

The problem is that the last filter do ignores all other filters and the result is get all data that does comply with the last filter.

标签： mysqlsqljoin

解决方案

AND之前评估，OR因为AND具有更高的优先级。使用括号覆盖运算符优先级：

WHERE
    PRECIO.forex_pair_price_time >= '2019-02-01 00:00' AND
    PRECIO.forex_pair_price_time <= '2019-02-07 01:00' AND 
    TIME (PRECIO.forex_pair_price_time) BETWEEN '06:00' AND '13:00' AND 
    PARES.forex_pair_id = 13 AND SENALES.forex_pair_signals_result = 1 AND
    (
        PRECIO.forex_pair_price_rsi >= '80.00' 
        OR
        PRECIO.forex_pair_price_rsi <= '20.00'
    )

或者你可以重写你的条件，所以它只是一个表达式：

    PRECIO.forex_pair_price_rsi not between '20.01' and '79.99'

推荐阅读