MYSQL 使用别名作为列，以防万一

Question

如何选择别名作为“date1”列值？我想选择显示月份和年份格式(如“Jan-18”)的别名。 MySQL 5.0 版。我尝试过，但没有成功:

SUM(CASE WHEN MONTH(date1) = 1  THEN quantity/1000 ELSE NULL END) AS date1

我的查询:

 SELECT 
           SUM(CASE WHEN MONTH(date1) = 1  THEN quantity/1000 ELSE NULL END) AS jan 

          ,SUM(CASE WHEN MONTH(date1) = 2 THEN quantity/1000 ELSE NULL END) AS feb

          ,SUM(CASE WHEN MONTH(date1) = 3 THEN quantity/1000 ELSE NULL END) AS mar

          ,SUM(CASE WHEN MONTH(date1) = 4 THEN quantity/1000 ELSE NULL END) AS apr

          ,SUM(CASE WHEN MONTH(date1) = 5 THEN quantity/1000 ELSE NULL END) AS may

          ,SUM(CASE WHEN MONTH(date1) = 6 THEN quantity/1000 ELSE NULL END) AS jun

          ,SUM(CASE WHEN MONTH(date1) = 7 THEN quantity/1000 ELSE NULL END) AS jul

          ,SUM(CASE WHEN MONTH(date1) = 8 THEN quantity/1000 ELSE NULL END) AS aug

          ,SUM(CASE WHEN MONTH(date1) = 9 THEN quantity/1000 ELSE NULL END) AS sep

          ,SUM(CASE WHEN MONTH(date1) = 10 THEN quantity/1000 ELSE NULL END) AS octo

          ,SUM(CASE WHEN MONTH(date1) = 11 THEN quantity/1000 ELSE NULL END) AS nov

          ,SUM(CASE WHEN MONTH(date1) = 12 THEN quantity/1000 ELSE NULL END) AS dece

    FROM
    data2


      WHERE
        date1 >= DATE_SUB('2018-09-11', INTERVAL 11 MONTH) AND  date1 <= DATE_FORMAT('2018-09-11', '%Y-%m-31')

    GROUP BY unit

Answer 1

其工作原理如下

sum(case when expression = true then 100 else 0 end) -- this will work fine

sum(case when expression = true then 100 else null end) -- this will also work - 
                                                       but gives you nulls even 
                                                       if there is one row
                                                       where the expression is 
                                                       false