mysql - SQL查询添加与子查询相关的where

Question

尝试获取子查询结果的 where 语句。下面的代码按照我想要的方式工作，但 AND low_36 != NULL 除外。我需要这个来停止 low_36 的输出为 NULL 的结果。

SELECT offers.*, 
(SELECT ROUND( MIN( price ) ,2) FROM o_prices WHERE o_id = offers.id AND months = 23 ) AS low_24, 
(SELECT ROUND( MIN( price ) ,2) FROM o_prices WHERE o_id = offers.id AND months = 35 ) AS low_36
(SELECT ROUND( MIN( price ) ,2) FROM o_prices WHERE o_id = offers.id AND months = 47 ) AS low_48 
FROM offers 
LEFT JOIN m_v ON v_v.code = offers.code 
LEFT JOIN m_f_t ON v.f_t_id = m_f_t.id 
LEFT JOIN m_t_t ON v.t_t_id = m_t_t.id 
WHERE status = 1 
AND low_36 != NULL 
LIMIT 2 OFFSET 0

我知道您不能在主 where 语句中使用子查询的结果，但这是我最接近正确结果的结果。感谢您的反馈

Answer 1

快速的方法是

SELECT *
FROM
    (SELECT offers.*, 
    (SELECT ROUND( MIN( price ) ,2) FROM o_prices WHERE o_id = offers.id AND months = 23 ) AS low_24, 
    (SELECT ROUND( MIN( price ) ,2) FROM o_prices WHERE o_id = offers.id AND months = 35 ) AS low_36
    (SELECT ROUND( MIN( price ) ,2) FROM o_prices WHERE o_id = offers.id AND months = 47 ) AS low_48 
    FROM offers 
    LEFT JOIN m_v ON v_v.code = offers.code 
    LEFT JOIN m_f_t ON v.f_t_id = m_f_t.id 
    LEFT JOIN m_t_t ON v.t_t_id = m_t_t.id 
    WHERE status = 1) a
WHERE low_36 IS NOT NULL 
LIMIT 2 OFFSET 0