sqlite - 在sqlite中使其简单

Question

这是我的sqlite声明：

select code ,  
case  when  (max(close)-min(close))/min(close)<0.1 then "grade1" 
when  (max(close)-min(close))/min(close) <0.2  then  "grade2"
when  (max(close)-min(close))/min(close) <0.3  then  "grade3"
else "grade4"  end  as  type;

(max(close)-min(close))/min(close)使用了3次，如何使我的查询语句更简单？

Answer 1

将计算结果放入子查询中：

SELECT code,
       CASE WHEN CloseRatio < 0.1 THEN 'grade1'
            WHEN CloseRatio < 0.2 THEN 'grade2'
            WHEN CloseRatio < 0.3 THEN 'grade3'
            ELSE                       'grade4'
       END AS type
FROM (SELECT code,
             (MAX(close) - MIN(close)) / MIN(close) AS CloseRatio
      FROM MyTable);