mysql条件排序_MySql条件排序

I have this table (simplified):

CREATE TABLE `my_table` (

`id` INT NOT NULL AUTO_INCREMENT ,

`item_name` VARCHAR(45) NULL ,

`price` DECIMAL(10,0) NULL ,

PRIMARY KEY (`id`) )

I need to select all items from the table, ordered this way:

1. items with price > 0.00 first, ordered by price ASC

2. items with price = 0.00 last, ordered by id

I tried this:

SELECT *

FROM my_table

WHERE 1

ORDER BY

CASE price WHEN !0.00 THEN price

ELSE id

END

ASC

And i get results like

item_name | price

----------|-------

foo | 150,00

bar | 0,00

baz | 500,00

hum | 0,00

How do I build the query to have

item_name | price

----------|-------

foo | 150,00

baz | 500,00

bar | 0,00

hum | 0,00

?

Thank you for your time

解决方案

This will do the trick..

SELECT *

FROM my_table

WHERE 1

ORDER BY

CASE price WHEN 0 THEN 1

ELSE -1

END ASC, price asc, id asc