mysql别名的未知列问题

问题描述：

当列是已创建的别名时，我不知道为什么会得到未知列.任何帮助都会很棒.

I can't figure out why i am getting an unknown column when the column is an alias that was created. Any help would be great.

代码:

SELECT DISTINCT 
    c.id, 
    ((SUM(c.width_feet)*12)+(SUM(c.width_inches))) AS width, 
    ((SUM(c.height_feet)*12)+(SUM(c.height_inches))) AS height 
    FROM carpets AS c 
    WHERE c.active = '1' 
    AND (width BETWEEN '0' AND '275') 
    AND (height BETWEEN '0' AND '599') 
    ORDER BY c.item_no

错误:

"where子句"中的未知列"width"

Unknown column 'width' in 'where clause'

答

您不能直接通过名称访问别名.

You cannot access the alias directly by name.

一种解决方案是将带有别名的查询包装在子查询中，然后在外部查询中引用别名:

One solution is to wrap the query with the aliases in a subquery, and then refer to the alias names in an outer query:

SELECT DISTINCT * 
FROM 
    (
     SELECT c.id, 
          ((SUM(c.width_feet)*12)+(SUM(c.width_inches))) AS width, 
          ((SUM(c.height_feet)*12)+(SUM(c.height_inches))) AS height
     FROM carpets AS c 
     WHERE c.active = '1'
    ) sub
WHERE (sub.width BETWEEN '0' AND '275') 
AND   (sub.height BETWEEN '0' AND '599') 
ORDER BY sub.item_no

相关推荐