如何在WHERE CLAUSE中使用CASE别名? [英] How to use CASE alias in WHERE CLAUSE?

问题描述

我正在尝试将"val"放入where子句，但它会返回错误:

I'm trying to put the "val" into where clause, but it returns error:

    Select ff.FormID, ff.FieldID, ff.FieldName, ff.Title, 
    ff.DefaultValue, fv.Value, 
    val = case fv.Value when null then cast(ff.DefaultValue as nvarchar) else fv.Value end,
    ff.DataType from
    (SELECT FormID, FieldID, FieldName, Title, DataType, DefaultValue FROM FormFields where FormID = '766A38D8-8058-42C6-AC46-A18C00D3C1DC' and DEL = 0) as ff
    left join
    (select FormID, FieldID, Value from FormValues where FormID = '766A38D8-8058-42C6-AC46-A18C00D3C1DC' and ItemID = 'FD63CCA2-C95F-4AB4-B84B-A220017027E7' and DEL = 0) as fv
    on ff.FormID = fv.FormID and ff.FieldID = fv.FieldID
where val is not null


Msg 207, Level 16, State 1, Line 9
Invalid column name 'val'.

感谢任何帮助:)

推荐答案

不允许在where子句中使用别名(在SQL Server中)，因为查询的逻辑执行顺序如下:

It is not allowed to use aliases in where clause (in sql server), because the order of logical execution of the query is as follows:

1. 从
2. 开启
3. 加入
4. 在哪里
5. 组别
6. 多维数据集或具有汇总
7. 拥有
8. 选择
9. DISTINCT
10. ORDER BY
11. TOP

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

您可以看到WHERE子句在SELECT之前执行，这就是为什么您不能从SELECT子句引用别名的原因

As you can see WHERE clause is being executed before SELECT, that's why you cannot refer to aliases from SELECT clause

尝试:

Select ff.FormID, ff.FieldID, ff.FieldName, ff.Title, 
ff.DefaultValue, fv.Value, 
val = case fv.Value when null then cast(ff.DefaultValue as nvarchar) else fv.Value end,
ff.DataType from
(SELECT FormID, FieldID, FieldName, Title, DataType, DefaultValue FROM FormFields where FormID = '766A38D8-8058-42C6-AC46-A18C00D3C1DC' and DEL = 0) as ff
left join
(select FormID, FieldID, Value from FormValues where FormID = '766A38D8-8058-42C6-AC46-A18C00D3C1DC' and ItemID = 'FD63CCA2-C95F-4AB4-B84B-A220017027E7' and DEL = 0) as fv
on ff.FormID = fv.FormID and ff.FieldID = fv.FieldID
where case fv.Value when null then cast(ff.DefaultValue as nvarchar) else fv.Value end is not null

这篇关于如何在WHERE CLAUSE中使用CASE别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！