无法识别 SQL 作为关键字

Question

我正在编辑一个存储过程以获取距离并从中对其进行排序。我正在使用的查询正在处理另一个存储过程。

SELECT  *,

( 
ACOS( COS( RADIANS( 41.993000000  ) ) 
* COS( RADIANS( u.real_users_lat ) )
* COS( RADIANS( u.real_users_long ) - RADIANS( -87.696207000 ) )
+ SIN( RADIANS( 41.993000000  ) )
* SIN( RADIANS( u.real_users_lat ) )
)
* 6371
) AS distance_in_km
FROM products
LEFT JOIN users u on u.id = products.created_by
LEFT JOIN product_categories on product_categories.product_id = products.id
WHERE products.starting_bid <= @price and product_categories.category_id = @category_id
ORDER BY 
CASE WHEN @sort_direction = 'asc' THEN products.date_created END asc,
CASE WHEN @sort_direction = 'desc' THEN products.date_created END desc,
CASE WHEN @sort_distance = 'nearest' THEN distance_in_km END desc,
CASE WHEN @sort_distance = 'farthest' THEN distance_in_km END asc
END

错误Invalid column name 'distance_in_km'.，但在我的其他存储过程中，它正在工作。

SELECT 
distinct Products.*, 
(
SELECT Count(bids.id) 
FROM bids
Where bids.product_id = products.id
) as bid_count ,
(
SELECT firebase_user_id
FROM Users
Where Users.id = created_by
) as seller_firebase_id , 
( 
ACOS( COS( RADIANS( 41.993000000  ) ) 
* COS( RADIANS( u.real_users_lat ) )
* COS( RADIANS( u.real_users_long ) - RADIANS( -87.696207000 ) )
+ SIN( RADIANS( 41.993000000  ) )
* SIN( RADIANS( u.real_users_lat ) )
)
* 6371
) AS distance_in_km

FROM Products
LEFT JOIN areas on areas.id = products.area
LEFT JOIN Product_categories on Product_categories.product_id = products.id
LEFT JOIN Users u on u.id = Products.created_by 
WHERE products.status <> 4 
ORDER BY distance_in_km DESC

END

请让我知道我做错了什么。

Answer 1

你没有做错任何事。SQL 服务器根本不允许以这种方式使用别名列。 直WHERE是可以的，但不能在表达式中使用它们。

不要问我为什么！

如果需要执行此操作，请将 SELECT 语句包装在 CTE 或内联视图中，它将正常工作。

最后还有一个额外的END;不过，也许这只是这个问题的复制和粘贴问题。

这应该使用内联视图关闭

SELECT  *,distance_in_km
FROM products
LEFT JOIN 
(select *, ( 
ACOS( COS( RADIANS( 41.993000000  ) ) 
* COS( RADIANS( u.real_users_lat ) )
* COS( RADIANS( u.real_users_long ) - RADIANS( -87.696207000 ) )
+ SIN( RADIANS( 41.993000000  ) )
* SIN( RADIANS( u.real_users_lat ) )
)
* 6371
) AS distance_in_km
from users) u on u.id = products.created_by
LEFT JOIN product_categories on product_categories.product_id = products.id
WHERE products.starting_bid <= @price and product_categories.category_id = @category_id
ORDER BY 
CASE WHEN @sort_direction = 'asc' THEN products.date_created END asc,
CASE WHEN @sort_direction = 'desc' THEN products.date_created END desc,
CASE WHEN @sort_distance = 'nearest' THEN distance_in_km END desc,
CASE WHEN @sort_distance = 'farthest' THEN distance_in_km END asc

埃拉尔珀的回答显示了CTE的一般技术。

Answer 2

> Jovin，您不能直接在创建的SQL中使用计算列

我的意思是，跟随是行不通

的

declare @sort varchar(5) = 'desc'
select [name], trim([name]) as tname
from sys.tables
order by 
case when @sort = 'desc' then tname end desc,
case when @sort = 'asc' then tname end asc

您可以执行以下操作之一

declare @sort varchar(5) = 'desc'
select [name], trim([name]) as tname
from sys.tables
order by 
case when @sort = 'desc' then trim([name]) end desc,
case when @sort = 'asc' then trim([name]) end asc

或者只使用 SQL CTE 表达式

declare @sort varchar(5) = 'desc'
;with cte as (
select [name], trim([name]) as tname
from sys.tables
) 
select *
from cte
order by 
case when @sort = 'desc' then tname end desc,
case when @sort = 'asc' then tname end asc