SQL服务器:使用XML
问题描述:
子查询无效列名我有了一个子查询给我Categories
列下面的查询。当我尝试添加WHERE
子句时,出现Invalid column name 'Categories'
错误。SQL服务器:使用XML
SELECT
l.LID,
Company,
Doors,
City,
Region,
Country,
Categories = STUFF((
SELECT
CONVERT(varchar(100), Junc_CatID) + ', '
FROM BND_ListingJunction_testing j
WHERE j.Junc_LID = l.LID
FOR XML PATH('')), 1, 2, '')
FROM BND_Listing_testing l
--FILTERS
WHERE
(Categories = '[querystring:filter-Category]' or '[querystring:filter-Category]'='All')
GROUP BY
LID,
Company,
Doors,
City,
Region,
Country
答
如果刚刚创建,则不能使用创建的列。
例如,这是错误的,因为税收是不存在
SELECT
id,
sales,
sales * tax as taxes
FROM sales
WHERE
taxes > 100
所以你需要我们的子查询或重复的代码。
SELECT *
FROM (SELECT id, sales, sales * tax as taxes FROM sales) T
WHERE
T.taxes > 100
OR
SELECT
id,
sales, sales * tax as taxes
FROM sales
WHERE
sales * tax > 100
的
+1
@DanielCorzo,gracias hermano。 –
可能的复制[参考别名(在SELECT计算)WHERE子句](http://*.com/questions/11182339/reference-alias-calculated-in- select-in-where-clause) – ps2goat
btw,我想你错过了'OR Categories = ....' –
你可以将你当前的语句嵌套在另一个语句中,然后用上面计算出的列值过滤外部语句。 'Select * from(/ * original select * /)x where x.Categories =/* original filters ... * /' – ps2goat