无法识别SQL作为关键字-SQLaskeywordisnotrecognized

1  

You're not doing anything wrong. SQL server simply does not allow the aliased column to be used in that way. A straight WHERE is ok, but you cannot use them in expressions.

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

Don't ask me why!

别问我原因!

If you need to do this then wrap your SELECT statement in a CTE or inline view and it will work fine.

如果您需要这样做,那么将您的SELECT语句包装在CTE或内联视图中,它将正常工作。

There's also an extra END at the end; perhaps that's just a copy-and-paste problem with the question, though.

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

This should be close using an inline view

这应该使用内联视图关闭

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

Eralper's answer Shows the CTE general technique.

Eralper的答案显示了CTE的一般技术。

0  

Jovin, you cannot use a computed column directly in the SQL it is created

Jovin,你不能直接在它创建的SQL中使用计算列

I mean, following will not work

我的意思是,以下将无法奏效

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

You can do one of the following

您可以执行以下操作之一

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

Or just use SQL CTE expression

或者只使用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