所以我想按价格订购产品- ASC
和DESC
,但有些产品将在数据库中填写discountprice
列,如果是这样-它们应该被考虑,而不是price
字段。
现在我有这个:SELECT * FROM products ORDER BY price ASC
,但是我怎样才能在特定产品的ORDER
条款中附加if (discountprice != '' AND discountprice != NULL) then use discount price
之类的东西呢?
你可以这样做:
SELECT (price-IFNULL(discountprice,0)) as finalprice FROM products
ORDER BY finalprice ASC
如果您试图按discountprice
列排序,您可以通过以下两种方式:
Using
IFNULL
:SELECT * FROM products ORDER BY IFNULL(discountprice, price) ASC
Using
COALESCE
:SELECT * FROM products ORDER BY COALESCE(discountprice, price) ASC
使用COALESCE
选择第一个非空值:
SELECT * FROM products
ORDER BY coalesce(discountprice, price) ASC
如果出于某种原因,您也有空字符串作为NULL,请使用常规CASE
:
SELECT * FROM products
ORDER BY CASE when discountprice is NOT NULL
or discountprice != '' THEN discountprice
else price end ASC
创建虚拟列。当没有折扣价格时,这一列必须为0。按此列排序
SELECT *,(price - discountprice) as order_price FROM products ORDER BY order_price ASC
如果需要,可以强制转换为整型。
试试这个
SELECT * FROM products ORDER BY
case when (discountprice != '' or discountprice != NULL)
then
(price-discountprice)
else
price
end
ASC