如何执行SQL ORDER BY曝光，焦点和ISO（数字不是字符串） [英] How can I perform SQL ORDER BY exposure, focal and iso (in numeric not string)

问题描述

曝光，焦点和等的列没有正确排序。看来他们都被解释为字符串，并按照这样排序。例如，如果我将曝光值排序，值可能如下所示：

1/800,1 / 800,1 / 800,1 / 80,1 / 675 ，1/640,1 / 60,1 / 500

如果我对Focal进行排序，它可以看起来像这样：

1.4,1.4,1.4,14.0,14.0,1.3,13.0,1.2,1.2,12.0,1.0,10.0 b
$ b

，如果按照ISO排序，可以看起来像这样：

800,800,800,80000,640,650,500,500，50

编辑：（和答案）在一个单一的SQL查询做这一切。

解决方案

p>我在Oracle上测试了这个

  with 
 w_data AS（
 select'1/800'exposure从双工会所有
从双工会全部选择'1/80'所有
从双工会全部选择'1/675'所有
从双工会全部选择'1/640'所有
从
）中选择'1/500'b 
从w_data 
中选择* 
 1 / to_number（substr（exposure，3，length（exposure）））
  

对于焦点你做 ORDER BY to_number（focal）和对于ISO也是一样。注意，曝光示例只有在前2个字符总是'1 /'时才起作用。如果你愿意的话，我可以把它改写成更一般的情况，比如2.9 / 700

The columns for Exposure, Focal, and Iso are not being sorted properly. It seems like they are all being interpreted as strings and sorted as such. For example, if I sort Exposure down, the values can look like this:

1/800, 1/800, 1/800, 1/80, 1/675, 1/640, 1/60, 1/500

If I sort the Focal, it can look like this:

1.4, 1.4, 1.4, 14.0, 14.0, 1.3, 13.0, 1.2, 1.2, 12.0, 1.0, 10.0

and if I sort by ISO, it can look like this:

800, 800, 800, 80, 80, 640, 640, 60, 500, 500, 50

EDIT: My other question (and answers) for doing all this in one single sql query.

解决方案

I tested this on Oracle

with 
   w_data AS(
      select '1/800' exposure from dual union all
      select '1/80'  from dual union all
      select '1/675' from dual union all
      select '1/640' from dual union all
      select '1/500' from dual 
   )
select *
  from w_data
 order by 1 / to_number(substr(exposure, 3, length(exposure)))   

For focal you do ORDER BY to_number(focal) and the same for ISO.

Note that exposure example will only work if first 2 characters are always '1/'. I can rewrite it for more general case, like '2.9/700', if you wish

这篇关于如何执行SQL ORDER BY曝光，焦点和ISO（数字不是字符串）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！