代码之家 › 专栏 › 技术社区 › Mark Pim

有没有更有效的方法来执行此SQL选择?

performance sorting sql

Mark Pim · 技术社区 · 16 年前

我有一个SQL查询:

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY CASE IMAGENAME
  WHEN 'IMG1' THEN 1
  WHEN 'IMG2' THEN 2
  WHEN 'IMG3' THEN 3
  WHEN 'IMG4' THEN 4
  WHEN 'IMG5' THEN 5
  WHEN 'IMG6' THEN 6
  ELSE 7
END

我不能保证ImageName列表将按字母顺序排列,因此使用case语句,但我更喜欢在DB中排序,而不是在代码中排序,因为我更信任它们的排序代码,而不是我的:)

它需要相当普通的SQL,因为我们的目标是SQL Server和Oracle。

任何调整建议都会非常棒。

9 回复 | 直到 16 年前

Yann Ramin 16 年前

我不确定这在您的目标数据库中是否可用,但它可能至少可以作为一种更好方法的灵感。在MySQL中,您可以使用FIELD()函数执行以下操作:

SELECT * FROM IMAGES WHERE
  IMAGENAME IN ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
  ORDER BY FIELD(IMAGENAME, 'IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6');

函数的作用是:获取第一个参数,然后根据参数列表中的位置返回一个整数,如果参数列表中不存在,则返回零。这使您能够为查询创建完全自定义的排序。

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

gx. 16 年前

SELECT *
FROM IMAGES
WHERE IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY IMAGENAME ASC

您不需要去掉imagename;您只选择了上面提到的几个案例,OrderBy可以很好地处理order字符串。另外,“else”条件没有任何意义,因为它永远不会被使用,因为您去掉了where子句中的所有其他选项。

注意:这是基于您发布的查询,如果有更多的条件或可能性,您也应该给出它们。

jitbit 16 年前

你能脱掉衣服吗 IMG IMAGENAME 在写入数据库时,是否将其排序到新列,然后在新的仅数值列上排序?

例如:

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY IMAGENO
END

哪里 IMAGENO 1, 2, 3, 4, 5, 6 分别针对示例中的每一行。

Tom Haigh 16 年前

CAST(SUBSTRING(IMAGENAME, 4, LEN(IMAGENAME) -3) as INTEGER)

我认为您最好在表中添加某种列,并在写入时进行设置

Tony Andrews 16 年前

如果您的所有图像都命名为“IMG”,那么您应该能够将名称从char 4子串,然后将其转换为数字。我不知道SQL Server在这方面的功能,但在Oracle中它将是:

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY TO_NUMBER(SUBSTR(IMAGENAME,4));

Mark Pim 16 年前

我之前应该说过,我给出的字符串是伪字符串,实际字段的结尾没有数字,很遗憾,但是谢谢你的建议。

我会考虑添加一个数字列以加快排序。

Mark Pim 16 年前

有一种几乎等同于Christomas123在SQL Server(但不是ORACLE)中建议的方法,即:

ORDER BY CHARINDEX(IMAGENAME, 'A,B,C,D,E,F,G')

cindi cindi 16 年前

如果可以选择添加索引,则(在sql server上)如果在IMAGENAME上定义聚集索引,则数据将被预排序。即使行没有按正确的顺序排序,它们也至少会按IMAGENAME分组,这样排序应该更快。如果添加numecic列,请将聚集索引放在该列上。添加列的另一种方法是查找表。但你可能没有这些选择

Walter Mitty 16 年前

创建包含关系的新表

用('IMG1',1),('IMG2',2),('IMG3',3),。。。在查询中加入此表,并按SEQUENCENUMBER排序。