mysql case when like_mysql 中casewhen用法

需求：需要在查询出的字段中，判断这个某个字段是否为null，并指定想要的值，

SELECT

temp.gId id,temp.`name`,temp.point,temp.give_user_proportion,temp.sales_price,temp.cover_img,temp.limit_num,temp.price,temp.show_sale_price,temp.isPromotion is_promotionFROM(SELECTallgoods.gId,

allgoods.`name`,

allgoods.sale_num,

allgoods.point,

allgoods.give_user_proportion,

allgoods.sales_price,

allgoods.cover_img,

allgoods.modify_date,

allgoods.productId,pi.limit_num,pi.price,pi.show_sale_price,CASE

WHEN pi.price IS NULL THEN

FALSE

ELSE

TRUEEND ASisPromotionFROM(SELECTgood.id gId,

good.`name`,

good.sale_num,

good.point,

good.give_user_proportion,

produc.sales_price,

produc.cover_img,

produc.modify_date,

produc.id productIdFROM(SELECT

*

FROMgoods goodstempWHEREgoodstemp.is_del=FALSEAND goodstemp.check_status = 'configed'

AND goodstemp.category_id = 174976742484877312) goodLEFT JOIN(SELECT

*

FROMproduct p2WHEREp2.is_del=FALSEAND p2.is_putaway =TRUEAND p2.is_show =TRUE

) producON good.id =produc.goods_idWHEREgood.id=produc.goods_id

) allgoodsLEFT JOIN(SELECT

*

FROMpromotion promWHEREprom.is_del=FALSEAND TO_DAYS(NOW()) >=TO_DAYS(prom.start_date)AND TO_DAYS(NOW()) <=TO_DAYS(prom.end_date)AND prom.publish_status = 'published') promotiontempON allgoods.gId =promotiontemp.goods_idLEFT JOIN promotion_item pi ON promotiontemp.id = pi.promotion_id

)temp

ORDER BY

temp.isPromotion DESC

结果：

当你的表示myisam时：

SELECT * FROM tbl -- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.

大致意思为，一个myisam引擎表在没有任何的删除，修改操作下，执行 select 不带order by，那么会按照插入顺序进行排序。

If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.

对于innodb引擎表来说，在相同的情况下，select 不带order by，会根据主键来排序，从小到大