是否对文本数据类型的数字使用MIN？

Question

我意识到这是一个糟糕的表格设计(不是我的)。

假设要使用除0.00之外的MIN(Biweekly_High_Rate)

+--------------------+
| Biweekly_High_Rate |
+--------------------+
| $0.00              |
| $15.00             |
| $25.00             |
| $50.00             |
| $100.00            |
| $100.00            |
| $200.00            |
| $500.00            |
| $0.00              |
| $10630.00          |
| $9175.00           |
| $4142.00           |
| $5242.00           |
| $3293.00           |
| $4496.00           |
| $4676.00           |
| $4762.00           |
| $11255.00          |
| $10376.00          |
| $9096.00           |
| $9456.00           |
| $9641.00           |
| $7392.00           |
| $7687.00           |
| $7835.00           |
+--------------------+

正在运行

Select min(Biweekly_high_Rate)
From salary_range_by_job_classification
Where (biweekly_high_Rate != "$0.00");

返回

+-------------------------+
| min(Biweekly_high_Rate) |
+-------------------------+
| $100.00                 |
+-------------------------+

它应该是15.00美元

现在我明白为什么会发生这种事了。问题是如何执行查询以返回$15.00？

Answer 1

一种方法是先按长度排序，然后按值排序--然后将值限制为一行：

Select Biweekly_high_Rate
From salary_range_by_job_classification
Where biweekly_high_Rate <> '$0.00'
order by length(Biweekly_high_Rate) asc, Biweekly_high_Rate asc
limit 1;

这是因为字符串具有相同的格式--一个前导'$'和两个小数位。

注意:有些数据库使用len()，有些数据库使用length()作为字符串长度。

Answer 2

试试这个：...

WITH
-- your input ...
salary_range_by_job_classification(biweekly_high_rate) as (
          SELECT '$0.00'
UNION ALL SELECT '$15.00'
UNION ALL SELECT '$25.00'
UNION ALL SELECT '$50.00'
UNION ALL SELECT '$100.00'
UNION ALL SELECT '$100.00'
UNION ALL SELECT '$200.00'
UNION ALL SELECT '$500.00'
UNION ALL SELECT '$0.00'
UNION ALL SELECT '$10630.00'
UNION ALL SELECT '$9175.00'
UNION ALL SELECT '$4142.00'
UNION ALL SELECT '$5242.00'
UNION ALL SELECT '$3293.00'
UNION ALL SELECT '$4496.00'
UNION ALL SELECT '$4676.00'
UNION ALL SELECT '$4762.00'
UNION ALL SELECT '$11255.00'
UNION ALL SELECT '$10376.00'
UNION ALL SELECT '$9096.00'
UNION ALL SELECT '$9456.00'
UNION ALL SELECT '$9641.00'
UNION ALL SELECT '$7392.00'
UNION ALL SELECT '$7687.00'
UNION ALL SELECT '$7835.00'
)
SELECT
  -- the first character is the dollar sign. 
  -- the rest is a numeric literal. Cast that as a DECIMAL(9,2).
  -- so start from the second character of the string, using SUBSTR().
  -- then use the same cast and filter out zeroes.
  MIN(CAST(SUBSTR(biweekly_high_rate,2) AS DECIMAL(9,2))) AS minrate
FROM salary_range_by_job_classification
WHERE CAST(SUBSTR(biweekly_high_rate,2) AS DECIMAL(9,2)) > 0
;
-- out  minrate 
-- out ---------
-- out    15.00

Answer 3

使用

    Select * from table where 
    Biweekly_high_Rate not like '$0.0%'
    order by 
   substr(2,Biweekly_high_Rate, 
    length(Biweekly_high_Rate)) 
     Limit 1