子查询大小写语句SQL中无法识别别名

Question

我有以下查询，运行良好，当没有填充的地方，但我有困难获得正确的引用的数据在哪里。

代码如下:

SELECT g.Id as Id,                                                                                               
       g.Printed as Printed,
       g.CreatedDate as CreatedDate , 
       case when (                            
           Select Count(Distinct custSubGA.CustomerId) from PickingAssignment_PickingAssignmentUserGroup custSubG 
              join PickingAssignment custSubGA on custSubGA.Id = custSubG.PickingAssignmentId
                      join Customer custSub2C on custSubGA.CustomerId = custSub2C.Id
                            where custSubG.PickingAssignmentUserGroupId = Max(g.Id)
                    ) > 1 
                    then 'Multiple' 
       else (
           Select Max(custSub2C.Name) from PickingAssignment_PickingAssignmentUserGroup custSub2G 
               join PickingAssignment custSub2GA on custSub2G.PickingAssignmentId = custSub2GA.Id                               
               join Customer custSub2C on custSub2GA.CustomerId = custSub2C.Id
               where custSub2G.PickingAssignmentUserGroupId = Max(g.Id) 
       ) end  as CustomerName 
from PickingAssignmentUserGroup g  
where   CustomerName like 'exet%'  

我还尝试使用"where custSub2C。像'exet%'这样的名称也不起作用。多部分标识符custSub2C。Name不能被绑定，这是我得到的。

这是我第一次尝试在SQL中使用case语句，所以有可能我做得非常非常错误。

任何帮助都太好了。如果您需要更多的信息，请告诉我。

Answer 1

不能在同一级别的where语句中使用列别名。在大多数数据库中，您只需使用子查询:

select *
from (SELECT g.Id as Id, g.Printed as Printed, g.CreatedDate as CreatedDate , 
             case when (Select Count(Distinct custSubGA.CustomerId)
                        from PickingAssignment_PickingAssignmentUserGroup custSubG join
                             PickingAssignment custSubGA
                             on custSubGA.Id = custSubG.PickingAssignmentId join
                             Customer custSub2C
                             on custSubGA.CustomerId = custSub2C.Id
                        where custSubG.PickingAssignmentUserGroupId = Max(g.Id)
                    ) > 1 
                    then 'Multiple' 
                  else (Select Max(custSub2C.Name)
                        from PickingAssignment_PickingAssignmentUserGroup custSub2G join 
                             PickingAssignment custSub2GA
                             on custSub2G.PickingAssignmentId = custSub2GA.Id join                           
                             Customer custSub2C
                             on custSub2GA.CustomerId = custSub2C.Id
                        where custSub2G.PickingAssignmentUserGroupId = Max(g.Id) 
                       )
             end  as CustomerName
      from PickingAssignmentUserGroup g  
     ) t
where   CustomerName like 'exet%'  

Answer 2

正如Gordon已经指出的那样，在WHERE之后解析列别名，但也可以将CASE中的子查询移动到FROM中，并且，作为唯一的区别是SELECT中的字段，合并它们。

SELECT g.Id as Id,
       g.Printed as Printed,
       g.CreatedDate as CreatedDate , 
       CASE WHEN CM.CustomerCount > 1 THEN 'Multiple' 
            ELSE CM.Name 
       END as CustomerName
FROM   PickingAssignmentUserGroup g
       OUTER APPLY (
             SELECT Max(custSub2C.Name) Name
                  , Count(Distinct custSubGA.CustomerId) CustomerCount
             FROM   PickingAssignment_PickingAssignmentUserGroup custSub2G 
                    INNER JOIN PickingAssignment custSub2GA 
                       ON custSub2G.PickingAssignmentId = custSub2GA.Id
                    INNER JOIN Customer custSub2C 
                       ON custSub2GA.CustomerId = custSub2C.Id
             WHERE  custSub2G.PickingAssignmentUserGroupId = Max(g.Id)) CM
WHERE  CM.Name like 'exet%' AND CM.CustomerCount = 1