按任意顺序对SQL行输出进行排序? [英] Sorting SQL row output per an arbitrary order?

问题描述

因此，在数据库中，我存储了乐器名称(以及其他各种属性).假设id是主键，而name是唯一键.

So, in my database, I store musical instrument names (and various other attributes). Let's say id is the primary key, and name is a unique key.

在PHP脚本中，我按其乐器类选择项，如下所示:

In a PHP script, I select items by their instrument class, like so:

$name = mysql_real_escape_string($_POST['name']);
$row = mysql_fetch_row(mysql_query("SELECT * FROM `instruments` WHERE name LIKE '%$name%' ORDER BY id"));

结果表:

id    name
1     "Flute 2"
2     "Bass Flute"
3     "Flute 10"
4     "Flute 7"

这使我只需查询萨克斯管"即可选择整个乐器系列，例如女高音萨克斯管"，中音萨克斯管"等.

This allows me to select the entire family of instruments, like the "Soprano Saxophone", "Alto Saxophone", etc, just by querying "Saxophone".

在该特定示例中，结果按其ID(可以假定为auto_incremented)排序.更理想的是按字母顺序排序，是吗?

In that particular example, the results are orered by their id (which you can assume is auto_incremented). More ideal would be ordering by alphabetical order, yes?

id    name
2     "Bass Flute"
3     "Flute 10"
1     "Flute 2"
4     "Flute 7"

这很好，但是作为音乐家，他们喜欢与数据库管理员纠缠不休，并且不乐意让Flute结果中的"Flute"上方列出"Bass Flutes"，并且确实如此不要在长笛2"之前列出长笛10".

That works fine, but being musicians, they like to screw around with the DB admin, and don't take kindly to having "Bass Flutes" listed above "Flute" in the Flute results, and really don't take kindly to having "Flute 10" listed before "Flute 2".

因此，看到没有ORDER BY score_order(那太容易了，不是吗...)，我该如何引入某种订购系统来正确显示工具?

So, seeing as there's no ORDER BY score_order (that would be too easy, wouldn't it...), how could I introduce some sort of ordering system to display instruments properly?

集思广益后，我想到了一种解决方案: 我可以再添加一列整数类型，并根据其重要性对乐器进行排名"(即Piccolos为"1"，长笛为"2"等):

Upon brainstorming, I came upon one solution: I could add one more column of type integer, and "rank" the instruments based on their importance (that is, Piccolos would be "1", Flutes "2", etc):

... nts` WHERE name LIKE '%$name%' ORDER BY rank, name"));

id    name           rank
3     "Flute 10"     2
1     "Flute 2"      2
4     "Flute 7"      2
2     "Bass Flute"   5

但是，这不能说明长笛10"排在长笛2"之前的事实，按字母数字.

However, this doesn't account for the fact that "Flute 10" comes before "Flute 2", alphanumerically.

理想结果表(按rank，然后按name排序):

The ideal result table (ordered by rank, and then by name):

id    name           rank
6     "Piccolo"      1
1     "Flute 2"      2
4     "Flute 7"      2
3     "Flute 10"     2
5     "Alto Flute"   4
2     "Bass Flute"   5

有没有一种方法可以通过分析整个数字而不是一个数字来获得SQL订单记录?

Is there a way to have SQL order records by analysing the entire number, instead of number by number?

推荐答案

我尚不清楚您理想的排序顺序是什么，但是您可能应该在数据库表中添加一个额外的列.您说ORDER BY score_order太简单了，但是为什么不添加一个明确的score_order字段和顺序呢?

It's not totally clear to me what your ideal sort order would be, but you should probably just make it an extra column in your database table. You say ORDER BY score_order would be too easy, but why not add an explicit score_order field and order by that?

这篇关于按任意顺序对SQL行输出进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！