根据满足哪个条件对行进行排序? [英] Order rows according to which condition is met?

问题描述

我有一个非常简单的问题:是否可以根据满足哪个条件对检索到的行进行排序?例如，我有一张人表，我想检索所有姓名以I"开头、以ster"结尾或包含lo"的人，并根据满足这些条件的条件进行排序.首先匹配第一个条件的行，然后匹配第二个条件的行，依此类推.(不重复:如果一行满足第一个条件，第二个条件就不应该再次显示)

I have a pretty simple question: Is it possible to order the rows retrieved according to the which condition is met? For example, I have a table of people, and I want to retrieve all people whose names begin with an "I", or end with an "ster", or contain "lo", ordered according to which condition of these is met. First the rows that match the first condition, then the rows that match the second, and so on. (Without duplicated: if a row meets the first condition, it shouldn't show again for the second condition)

我使用 Visual C#，并使用 MS Access 管理数据库.(如果重要的话，文件格式是 .mdb)

I work with Visual C#, and I manage the DB with MS Access. (The file format is .mdb, if that matters)

谢谢=)

推荐答案

这样的事情应该可行:

SELECT * FROM people
ORDER BY
  CASE WHEN name LIKE "I%" THEN 0
    WHEN name LIKE "%ster" THEN 1
    WHEN name LIKE "%lo%" THEN 2
    ELSE 3
  END ASC;

在 Access 中，您可能不得不求助于嵌套的 IIF():

In Access you may have to resort to nested IIF()s though:

ORDER BY
  IIF( name LIKE "I%",     0,
  IIF( name LIKE "%ster%", 1,
  IIF( name LIKE "%lo%",   2,
    3
  ) ) ) ASC

这篇关于根据满足哪个条件对行进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！