SQL 语句中 case when 的优化

Question

select t.FD_INDEXNAME 名称,
       t.FD_SCORE 得分,
       t.FD_INDEXVALUE 档位,
       case 
       when  t.FD_SCORE = (select max(n.FD_SCORE) from ns_assets_indexes n where n.FD_STEPID in (select fd_id from ns_assets_step s where s.fd_rateid = '098266ca-5e83-48f4-ae44-99fbf2df57a3')
         and n.FD_INDEXCATEGORY = '保值性' and n.fd_indexname != '保值性') then  t.FD_QUALITATIVE_OPTIONS
       when  t.FD_SCORE = (select min(n.FD_SCORE) from ns_assets_indexes n where n.FD_STEPID in (select fd_id from ns_assets_step s where s.fd_rateid = '098266ca-5e83-48f4-ae44-99fbf2df57a3')
         and n.FD_INDEXCATEGORY = '保值性' and n.fd_indexname != '保值性') then  t.FD_QUALITATIVE_OPTIONS
       end 描述
 from(
       select n.FD_INDEXNAME,
              n.FD_SCORE,
              n.FD_INDEXVALUE,
              n.FD_QUALITATIVE_OPTIONS
          from ns_assets_indexes n
         where n.FD_STEPID in
               (select fd_id
                  from ns_assets_step s
                 where s.fd_rateid = '098266ca-5e83-48f4-ae44-99fbf2df57a3')
         and n.FD_INDEXCATEGORY = '保值性'
         and n.fd_indexname != '保值性') t

Answer 1

按你的逻辑，是只在最大值和最小值的时候取字段，修改为下面这样应该好点：

SELECT
    t.FD_INDEXNAME 名称,
    t.FD_SCORE 得分,
    t.FD_INDEXVALUE 档位,
    CASE
        WHEN t.FD_SCORE = t1.FD_SCORE_max or t.FD_SCORE=t1.FD_SCORE_min then t.FD_QUALITATIVE_OPTIONS
    END 描述
FROM
(
SELECT
    n.FD_INDEXNAME,
    n.FD_SCORE,
    n.FD_INDEXVALUE,
    n.FD_QUALITATIVE_OPTIONS,
    n.FD_STEPID  --增加字段
FROM
    ns_assets_indexes n
WHERE
    n.FD_STEPID IN
    (
        SELECT
            fd_id
        FROM
            ns_assets_step s
        WHERE
            s.fd_rateid = '098266ca-5e83-48f4-ae44-99fbf2df57a3')
AND n.FD_INDEXCATEGORY = '保值性'
AND n.fd_indexname != '保值性') t 
left join (
SELECT
    n.FD_STEPID,MAX(n.FD_SCORE) as FD_SCORE_max,MIN(n.FD_SCORE) as FD_SCORE_min
FROM
    ns_assets_indexes n
WHERE
    n.FD_STEPID IN
    (
        SELECT
            fd_id
        FROM
            ns_assets_step s
        WHERE
            s.fd_rateid = '098266ca-5e83-48f4-ae44-99fbf2df57a3')
AND n.FD_INDEXCATEGORY = '保值性'
AND n.fd_indexname != '保值性'
group by n.FD_STEPID
) t1 on t.FD_STEPID=t1.FD_STEPID