mysql - 如何在同一SQL语句中引用新字段？

问题在于“ con_relief”和“ total_ded”，因为它们在工资表中不存在，但已在前几行中设置。如何在同一SQL语句中引用这些新字段？

CREATE VIEW `assessement` AS

SELECT con_income,
    (CASE
        WHEN con_income<304347.83 THEN (0)
        ELSE (200000+(0.2*con_income))
    END)
AS con_relief,

(con_relief+lap+nhis+nhf+pension+gratuity) AS 'total_ded',
(con_income-total_ded) AS 'chargeable'


FROM payroll;

最佳答案

使用外部查询中的计算列作为Sub-select

SELECT con_income,
        CASE
           WHEN con_income < 304347.83 THEN ( 0 )
           ELSE ( 200000 + ( 0.2 * con_income ) )
         END AS con_relief,
        CASE
            WHEN con_income<304347.83 THEN (0)
            ELSE (200000+(0.2*con_income))
        END + lap + nhis + nhf + pension + gratuity  AS 'total_ded',
        con_income - ( CASE WHEN con_income<304347.83 THEN (0)
                            ELSE (200000+(0.2*con_income))
                        END + lap + nhis + nhf + pension + gratuity )  AS 'chargeable'

另一个解决方法是为子查询创建一个view，然后使用它。

注意：嵌套视图将出现性能问题

CREATE VIEW Sub_view
AS
  SELECT con_income,
         CASE
           WHEN con_income < 304347.83 THEN ( 0 )
           ELSE ( 200000 + ( 0.2 * con_income ) )
         END  AS con_relief,
         CASE WHEN con_income < 304347.83 THEN ( 0 )
              ELSE ( 200000 + ( 0.2 * con_income ) ) END
              + lap + nhis + nhf + pension + gratuity AS 'total_ded'
  FROM   payroll

CREATE VIEW assessement
AS
  SELECT con_income,
         con_relief,
         con_relief + total_ded                  AS total_ded,
         con_income - ( con_relief + total_ded ) AS chargeable
  FROM   sub_view a