无法在WHERE中添加别名的解决方案

Question

我知道在SQL server中不能在WHERE子句中使用别名作为列名。虽然我目前拥有的过滤器似乎不起作用，但我已经多次尝试通过扩展名(仅LDF和MDF)过滤文件，但它不起作用。以下是我所拥有的，我想知道是否有人知道实现这一目标的方法。

我想在我的输出集中是在FileType列中具有LDF或MDF的行。

这是我尝试过的

SELECT DISTINCT
        CWV.SystemName ,
        CIL.CIL_INSTANCE_NAME AS [Instance Name] ,
        CWV.DriveLetter ,
        UPPER(RIGHT(CIDFP.physical_name, 3)) AS FileType ,
        CIDFP.physical_name AS [Physical Name]
FROM    CCS_InstanceDatabaseFilesProperty AS CIDFP
        INNER JOIN CCS_INSTANCE_LIST AS CIL ON CIL.CIL_ID = CIDFP.CIL_ID
        INNER JOIN CCS_InstanceServerProperty AS CSP ON CSP.CIL_ID = CIDFP.CIL_ID
        LEFT OUTER JOIN CCS_Win32_Volume AS CWV ON CIL.CSL_ID = CWV.CSL_ID
                                                   AND LEFT(CIDFP.physical_name,
                                                      2) = CWV.DriveLetter
WHERE   ( CWV.SystemName IS NOT NULL )
        AND ( CWV.DriveLetter IS NOT NULL )
        OR RIGHT(CIDFP.physical_name, 3) = 'mdf'
        OR RIGHT(CIDFP.physical_name, 3) = 'ldf'

提前感谢您的输入/回答。

Answer 1

将WHERE子句改为:

WHERE   ( CWV.SystemName IS NOT NULL )
        AND ( CWV.DriveLetter IS NOT NULL )
        AND (RIGHT(CIDFP.physical_name, 3) = 'mdf'
        OR RIGHT(CIDFP.physical_name, 3) = 'ldf')

Answer 2

您的AND/OR逻辑没有正确分组

如果你想要驱动器不是空AND physical_name = 'mdf' OR 'ldf'你可以改变它像这样:

WHERE   CWV.SystemName IS NOT NULL
    AND CWV.DriveLetter IS NOT NULL 
    AND RIGHT(CIDFP.physical_name, 3) in ('mdf', 'ldf')