如何按特定顺序列出mysql查询结果? [英] how to list mysql query result in particular sequence?

问题描述

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

the problem #14 from SQLZoo for 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查询结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！