SQLServer里将查询出的一个字段 as 为新名称，再以这个新字段作为条件加入 where 报错？

Question

SELECT Id,Yssl,Ysbj,YsslSh,ZcPrice,FcPrice,RgPrice,PriceType,Mark,

       (SELECT MakeType FROM [3d_De] WHERE Id=[3d_Cymb_Item].De_Id) AS MakeType

FROM [3d_Cymb_Item] 

WHERE Cymb_Id=833 AND Tree Like'%,11088,%' AND Unit<>'' AND MakeType=1

--我查询出一个新字段 AS 为新的名称

--在 WHERE 里面作为条件就报错、但去掉它就正常

--这个条件又是必要的、需要根据 MakeType 来区分数据呀

--这咋处理呀 (=_=||

Answer 1

在外面套一层子查询

SELECT * FROM (
    SELECT Id,Yssl,Ysbj,YsslSh,ZcPrice,FcPrice,RgPrice,PriceType,Mark,
    
           (SELECT MakeType FROM [3d_De] WHERE Id=[3d_Cymb_Item].De_Id) AS MakeType
    
    FROM [3d_Cymb_Item] 
    
    WHERE Cymb_Id=833 AND Tree Like'%,11088,%' AND Unit<>'' 
)
WHERE MakeType=1

不过我更建议用内联的方式

SELECT Id,Yssl,Ysbj,YsslSh,ZcPrice,FcPrice,RgPrice,PriceType,Mark,

       MakeType

FROM [3d_Cymb_Item] A

INNER JOIN [3d_De] B ON B.Id=A.De_Id

WHERE A.Cymb_Id=833 AND A.Tree Like'%,11088,%' AND A.Unit<>'' AND B.MakeType=1