关于一个mysql查询排序问题

Question

一张商品表(主表) item
一张商品库存表(关联表) item_stock

商品表对应多个商品库存表，现有一个关于商品列表的查询SQL，要求库存为0的商品排列在最后显示(库存表里面有商品的itemId字段)，其他的原先排序逻辑不改变

Answer 1

select * ,( select count(*) from item_stock as is2 where is2.itemid = i.id ) as sum
from item as i
join item_stock as is on i.id = is.itemid
group by i.id
order by sum desc

Answer 2

看看是不是这样, 有库存的按item表排序在前面， 没库存的在后面

原始测试数据

Answer 3

select distinct a, b,c from item left join itemstock on item.id = itemstock.itemid
where itemstock.id is not null order by somthing
union
select distinct  a,b,c from item left join itemstock on item.id = itemstock.itemid
where itemstock.id is null order by something