mysql的查询语句，where条件后面跟if语句

自己在写商城的时候碰到一个的感觉挺有意思的查询，记录下来，同时发布出来，希望可以帮到有需要的同学where 条件后面跟if判断where1=1AND (if( automobile_price IS NULL andtrain_price IS NULL ,price >= #{command.minPrice,jdbcType=DECIMAL},price < 0)o

cj505061767

3288人浏览 · 2022-01-14 09:23:25

cj505061767 · 2022-01-14 09:23:25 发布

自己在写商城的时候碰到一个的感觉挺有意思的查询，记录下来，同时发布出来，希望可以帮到有需要的同学
where 条件后面跟if判断

where  1=1 
            AND (if( automobile_price IS NULL and  train_price IS NULL ,price &gt;= #{command.minPrice,jdbcType=DECIMAL},price &lt; 0)
            or if(automobile_price IS NULL and train_price IS NOT NULL,( train_price + price ) &gt;= #{command.minPrice,jdbcType=DECIMAL},price &lt; 0 )
            or if(automobile_price IS NOT NULL and train_price IS NULL,( automobile_price + price ) &gt;= #{command.minPrice,jdbcType=DECIMAL} ,price &lt; 0)
            or if(automobile_price IS NOT NULL and train_price IS NOT NULL,GREATEST( automobile_price + price, train_price + price ) &gt;= #{command.minPrice,jdbcType=DECIMAL} ,price &lt; 0 )
            )
            AND (IF(automobile_price IS NULL and train_price IS NULL,price &lt;= #{command.maxPrice,jdbcType=DECIMAL},price &lt; 0 )
            or if(automobile_price IS NULL and train_price IS NOT NULL,( train_price + price ) &lt;= #{command.maxPrice,jdbcType=DECIMAL},price &lt; 0)
            or if(automobile_price IS NOT NULL and train_price IS NULL,( automobile_price + price ) &lt;= #{command.maxPrice,jdbcType=DECIMAL},price &lt; 0)
            or if(automobile_price IS NOT NULL and train_price IS NOT NULL,LEAST( automobile_price + price, train_price + price ) &lt;= #{command.maxPrice,jdbcType=DECIMAL},price &lt; 0 )
            )

大体的意思就是 if(条件，条件为true执行，条件为false执行)

另外记录一些关键词 LEAST( automobile_price + price, train_price + price ) 取2者中最小的值
GREATEST( automobile_price + price, train_price + price ) 取2者中最大的值
ifnull(automobile_price,0) 如果值为null,则赋值0