如何在SELECT中赋值长表达式？

Question

怎样才能不重复使用avg(examinee_grade_science + ...)？

如您所见，我在CASE表达式下重复了一遍。我可以将它赋给一个变量并在CASE表达式中使用它吗？

我试过使用SET (set @ave_grade = examine_grade...)，但它不起作用。有没有可能呢？

select  
    examinee_grade_science as "Science Grade", 
    examinee_grade_math as "Math Grade", 
    examinee_grade_literature as "Literature Grade",
    examinee_grade_science + examinee_grade_math +  examinee_grade_literature as "TOTAL",
    avg(examinee_grade_science + examinee_grade_math + examinee_grade_literature) as "AVERAGE",  
    case when avg(examinee_grade_science + examinee_grade_math + examinee_grade_literature) < 93 
        then 'PASSED' 
        else 'FAILED' 
    end as "REMARKS"
from examinee

Answer 1

使用子查询计算平均值，然后使用分配给外部查询中计算的平均值的别名检查通过/失败

SELECT *,
 case when AVERAGE < 93 
                    then 'PASSED' 
                    else 'FAILED' 
              end as "REMARKS"
FROM
(
    select  
                  examinee_grade_science as "Science Grade", 
                  examinee_grade_math as "Math Grade", 
                  examinee_grade_literature as "Literature Grade",
                  examinee_grade_science + examinee_grade_math +  examinee_grade_literature as "TOTAL",
                  avg(examinee_grade_science + examinee_grade_math + examinee_grade_literature) as "AVERAGE"



    from examinee
) t

Answer 2

我将使用CROSS APPLY为表达式创建别名：

select
    examinee_grade_science as "Science Grade", 
    examinee_grade_math as "Math Grade", 
    examinee_grade_literature as "Literature Grade",
    grade_sum as "TOTAL",
    avg(grade_sum) as "AVERAGE",
    case when avg(grade_sum) < 93 
        then 'PASSED' 
        else 'FAILED' 
    end as "REMARKS"
from
    examinee
    CROSS APPLY
    (
        SELECT 
            examinee_grade_science + examinee_grade_math + examinee_grade_literature AS grade_sum
    ) AS A
;

Answer 3

你有没有试过使用平均别名以防万一？在Teradata中，可以在case语句中使用别名。请检查它是否在SQL server中？

select  
              examinee_grade_science as "Science Grade", 
              examinee_grade_math as "Math Grade", 
              examinee_grade_literature as "Literature Grade",
              examinee_grade_science + examinee_grade_math +  examinee_grade_literature as "TOTAL",
              avg(examinee_grade_science + examinee_grade_math + examinee_grade_literature) as "AVERAGE",


              case when AVERAGE < 93 
                    then 'PASSED' 
                    else 'FAILED' 
              end as "REMARKS"


from examinee