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_promotion
FROM
    (
        SELECT
            allgoods.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
            TRUE
        END AS isPromotion
        FROM
            (
                SELECT
                    good.id gId,
                    good.`name`,
                    good.sale_num,
                    good.point,
                    good.give_user_proportion,
                    produc.sales_price,
                    produc.cover_img,
                    produc.modify_date,
                    produc.id productId
                FROM
                    (
                        SELECT
                            *
                        FROM
                            goods goodstemp
                        WHERE
                            goodstemp.is_del = FALSE
                        AND goodstemp.check_status = 'configed'
                        AND goodstemp.category_id = 174976742484877312
                    ) good
                LEFT JOIN (
                    SELECT
                        *
                    FROM
                        product p2
                    WHERE
                        p2.is_del = FALSE
                    AND p2.is_putaway = TRUE
                    AND p2.is_show = TRUE
                ) produc ON good.id = produc.goods_id
                WHERE
                    good.id = produc.goods_id
            ) allgoods
        LEFT JOIN (
            SELECT
                *
            FROM
                promotion prom
            WHERE
                prom.is_del = FALSE
            AND TO_DAYS(NOW()) >= TO_DAYS(prom.start_date)
            AND TO_DAYS(NOW()) <= TO_DAYS(prom.end_date)
            AND prom.publish_status = 'published'
        ) promotiontemp ON allgoods.gId = promotiontemp.goods_id
        LEFT 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，会根据主键来排序，从小到大