如何按特定顺序列出mysql查询结果?

本文介绍了如何按特定顺序列出mysql查询结果?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

SQLZoo的问题#14桌子诺贝尔奖(年，主题，获胜者)像显示1984年的获奖者和主题，并按主题和获奖者名称排序；但最后列出化学和物理学."

the problem #14 from SQLZoofor table nobel(yr, subject, winner)goes as "Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last."

我的解决方法是

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject,winner

但是它最后没有列出化学和物理学.有办法吗?以及如何按特定顺序对字段进行排序?

but it does not list chemistry and physics at last.is there a way to do so?and how to order a field in particular sequence?

推荐答案

使用可以使用CASE:

SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY
 CASE
    WHEN subject IN ('Physics','Chemistry') THEN 1
    ELSE 0
 END ASC,
 subject,
 winner

这与 Gordon Linoff解决方案主要相同，但是如果需要，可以使用更多规则将其扩展到其他主题. IN仅允许两种方式:false/true.

This is primary the same as Gordon Linoff Solution, but can be extended to another subjects if needed using more rules. IN allows only 2 ways: false/true.

ORDER BY
 CASE
    WHEN subject IN ('Physics','Chemistry') THEN 2
    WHEN subject IN ('Medicine','Literature') THEN 1
    ELSE 0
 END ASC,
 subject,
 winner

这篇关于如何按特定顺序列出mysql查询结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！