如何在 SQL Server 中的 where 子句中使用别名列名

子句在子句 （*） 之前处理，因此别名不可用。转到使用子查询或 CTE - 下面是一个 CTE：WHERESELECT

; with Distances as (
    select SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +   
 POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 
 AS DistanceFromAddress
    from tblProgram
)
select * from Distances where DistanceFromAddress < 2

---

（*） - 好吧，系统可以自由地对他们认为合适的操作进行重新排序，只要结果是“好像”SQL语句是按照特定的逻辑顺序处理的。当然，SQL Server 出现所有这些错误的地方是它产生错误的地方，因为子句中的行/值的转换问题应由子句消除。SELECTWHERE

select 
  SQRT(
    POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
    POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)
  ) * 62.1371192 AS DistanceFromAddress 
from tblProgram 
having DistanceFromAddress < 2

可以工作（尽管我认为不是，也没有 group by 子句）。

问题在于，您只能在子句中选择的表范围内使用名称。 是一个预过滤器，用于在选择行之前过滤掉行，因此字段定义中的此类表达式尚未执行，因此别名不可用。whereWhere

该子句在分组后用作后过滤器，可以使用查询中的别名，尽管恐怕您需要有一个实际的子句（不确定）。Havinggroup by

另一种方法是使用子选择（派生表或选择中的选择），首先选择每行的距离，然后仅选择这些结果中的相关距离。这将起作用：

select d.DistanceFromAddress
from
  (select 
    SQRT(
      POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
      POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)
    ) * 62.1371192 AS DistanceFromAddress 
  from tblProgram) d
where d.DistanceFromAddress < 2

或者您可以重复该表达式。这会使查询更难维护，但在某些情况下，这可能适合您。例如，如果您不想返回实际距离，而只想返回该距离处的兴趣点的名称。在这种情况下，您只需要在子句中使用表达式，在这种情况下，可维护性参数就消失了，而此解决方案是一个完美的替代方案。where

select 
  tblProgram.POIname
  /* Only if you need to return the actual value
  , SQRT(
    POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
    POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)
  ) * 62.1371192 AS DistanceFromAddress */
from tblProgram
where 
  -- Use this if you only want to filter by the value.
  SQRT(
    POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
    POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)
  ) * 62.1371192 < 2

不能在子句中使用别名列。您可以尝试使用派生表。也许是这样（对不起，未经测试）：WHERE

SELECT * 
FROM (
    SELECT SQRT( POWER( cast(Program_Latitude as float) 
                        - cast('41.5126237' as float), 2) 
               + POWER( cast(Program_Longitude as float) 
                        - cast('-81.6516411' as float), 2)
               ) * 62.1371192    AS DistanceFromAddress 
    from tblProgram 
    ) mytable
WHERE DistanceFromAddress < 2

我认为您只能使用 AS 来显示最终值。要进行比较，应从其中的另一个 select 语句返回 select。

喜欢：

SELECT a.disfromaddr FROM
 ( SELECT SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +   
 POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 
 AS DistanceFromAddress FROM tblProgram) 
a WHERE a.disfromaddr < 2

你可以试试这个。

select 
    t1.*,
    t2.brand_name,
    t3.category_name,
    if(
        t3.parent_category_id=0,
        t3.cat_key,
        (
            select 
              concat(t3b.cat_key,'/',t3.cat_key) 
            from master_category t3b 
            where t3b.category_id=t3.parent_category_id 
        )
    ) as cat_key,
    (
        select min(t4.product_MSP) 
        from trans_products t4  
        where t1.product_id=t4.product_id 
    ) as product_MSP,
    (
        select min(t4.product_MRP) 
        from trans_products t4 
        where t1.product_id=t4.product_id 
    ) as product_MRP 
from master_products t1,
master_brand t2,
master_category t3 
where t1.status=1 
and t2.status=1 
and t2.brand_id=t1.brand_id 
and t3.category_id=t1.category_id 
and t3.display_status=1 
and t1.category_id=6 
and product_MSP between '1000' and '500000'