选择中的mysql变量

Question

我可以在第二个选择中使用 num_daysopen

SELECT
  DATEDIFF(DATE(NOW()), DATE(dateopened)) AS num_daysopen,
  (28 - DATEDIFF(DATE(NOW()), DATE(dateopened))) as days_left
FROM table

下面的似乎不起作用，如果它起作用，它会比上面的更具可读性吗？

SELECT
  DATEDIFF(DATE(NOW()), DATE(dateopened)) AS num_daysopen,
  (28 - num_daysopen) as days_left
FROM table

Answer 1

不能在同一级别使用列别名。
所以添加一个派生表来增加一个层次

SELECT
   num_daysopen,
   (28 - num_daysopen) as days_left
FROM
   (
    SELECT 
       DATEDIFF(DATE(NOW()), DATE(dateopened)) AS num_daysopen
    FROM 
       table
   ) T

参见 "Problems with Column Aliases"在 MySQL 文档中