select * from (SELECT distinct mchanpintype,ztgif,zt FROM book where shenghe=1 and ztid=12 ) aa order BY CASE WHEN mchanpintype like '%MP3型%' THEN 1 WHEN mchanpintype like '%红外型%' THEN 2 WHEN mchanpintype like '%商务型%' THEN 3 WHEN mchanpintype like '%战斧型%' THEN 4 WHEN mchanpintype like '%智能备份%' THEN 5 END desc
你在case when 语句的最后面加一句else看看可不可以
考虑一下分两步写。 如: SELECT mchanpintype,ztgif,zt INTO #tmp FROM book where shenghe=1 and ztid=12 ORDER BY CASE WHEN mchanpintype like '%MP3型%' THEN 1 WHEN mchanpintype like '%红外型%' THEN 2 WHEN mchanpintype like '%商务型%' THEN 3 WHEN mchanpintype like '%战斧型%' THEN 4 WHEN mchanpintype like '%智能备份%' THEN 5 END desc-------------------------------------------------------- select distinct * from #tmp drop table #tmp
select * from (SELECT distinct mchanpintype,ztgif,zt FROM book where shenghe=1 and ztid=12)AA ORDER BY CASE WHEN mchanpintype like '%MP3型%' THEN 1 WHEN mchanpintype like '%红外型%' THEN 2 WHEN mchanpintype like '%商务型%' THEN 3 WHEN mchanpintype like '%战斧型%' THEN 4 WHEN mchanpintype like '%智能备份%' THEN 5 END desc
SELECT distinct mchanpintype,ztgif,zt FROM book where shenghe=1 and ztid=12 ORDER BY CASE WHEN mchanpintype like '%MP3型%' THEN 1 WHEN mchanpintype like '%红外型%' THEN 2 WHEN mchanpintype like '%商务型%' THEN 3 WHEN mchanpintype like '%战斧型%' THEN 4 WHEN mchanpintype like '%智能备份%' THEN 5 END,ztgif,zt desc
order BY
CASE WHEN mchanpintype like '%MP3型%' THEN 1
WHEN mchanpintype like '%红外型%' THEN 2
WHEN mchanpintype like '%商务型%' THEN 3
WHEN mchanpintype like '%战斧型%' THEN 4
WHEN mchanpintype like '%智能备份%' THEN 5
END
desc
如:
SELECT mchanpintype,ztgif,zt INTO #tmp FROM book where shenghe=1 and ztid=12
ORDER BY
CASE WHEN mchanpintype like '%MP3型%' THEN 1
WHEN mchanpintype like '%红外型%' THEN 2
WHEN mchanpintype like '%商务型%' THEN 3
WHEN mchanpintype like '%战斧型%' THEN 4
WHEN mchanpintype like '%智能备份%' THEN 5
END
desc--------------------------------------------------------
select distinct * from #tmp
drop table #tmp
ORDER BY
CASE WHEN mchanpintype like '%MP3型%' THEN 1
WHEN mchanpintype like '%红外型%' THEN 2
WHEN mchanpintype like '%商务型%' THEN 3
WHEN mchanpintype like '%战斧型%' THEN 4
WHEN mchanpintype like '%智能备份%' THEN 5
END
desc
ORDER BY
CASE WHEN mchanpintype like '%MP3型%' THEN 1
WHEN mchanpintype like '%红外型%' THEN 2
WHEN mchanpintype like '%商务型%' THEN 3
WHEN mchanpintype like '%战斧型%' THEN 4
WHEN mchanpintype like '%智能备份%' THEN 5
END,ztgif,zt
desc