SQL查询-无效的列名

Oualid 发表于 Dev

瓦利德

执行以下sql查询时出现错误。

SELECT 
    RTRIM(name) AS [Segment Name], 
    growth,groupid AS [Group Id], 
    filename AS [File   Name],
    CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
    CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
    CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
    CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent_Used]
FROM 
    sysfiles
WHERE 
    growth = 0 AND  Percent_Used > 60
ORDER BY 
    groupid DESC

错误提示：

消息207，级别16，状态1，第7行
无效的列名称“ Percent_Used”。

为什么会出现此错误？

维斯瓦纳特·达尔维（Vishwanath Dalvi）

如果看到查询的逻辑顺序处理，则在选择之前先评估where子句。要使其工作，您需要使用派生表概念。

1. FROM
2. ON
3. OUTER
4. **WHERE**
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. **SELECT**

http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

   Select * from
    (
        SELECT 
            RTRIM(name) AS [Segment Name], 
            growth,groupid AS [Group Id], 
            filename AS [File   Name],
            CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
            CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
            CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
            CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent_Used]
        FROM 
            sysfiles
    ) DT

        WHERE growth = 0 AND  Percent_Used > 60
        ORDER BY groupid DESC

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。