sql-server - 在 Where 子句中引用时，选择动态字符串具有不同的值

Question

我动态选择一个使用另一个字符串构建的字符串。所以，如果 string1='David Banner'，则 MyDynamicString 应该是 'DBanne'

Select 
...
, Left(
    left((select top 1 strval from dbo.SPLIT(string1,' ')) //first word
    ,1) //first character
    + (select top 1 strval from dbo.SPLIT(string1,' ') 
        //second word
        where strval not in (select top 1 strval from dbo.SPLIT(string1,' '))) 
,6) //1st character of 1st word, followed by up to 5 characters of second word
[MyDynamicString]
,...
From table1 Join table2 on table1pkey=table2fkey
Where MyDynamicString <> table2.someotherfield

我知道 table2.someotherfield 不等于动态字符串。但是，当我用完整的 left(left(etc.. 函数替换 Where 子句中的 MyDynamicString 时，它按预期工作。

我可以不在稍后的查询中引用这个字符串吗？我是否必须每次在 where 子句中使用 left(left(等.. 函数来构建它？

Answer 1

如果你按照上面的方式去做，那么答案是肯定的，你必须在where子句中重新创建它。

作为替代方案，您可以使用内联 View :

    Select 
    ...
    , X.theString
    ,...
    From table1 Join table2 on table1pkey=table2fkey
       , (SELECT 
          string1
         ,Left(
            left((select top 1 strval from dbo.SPLIT(string1,' ')) //first word
                 ,1) //first character
            + (select top 1 strval from dbo.SPLIT(string1,' ') 
            //second word
            where strval not in (select top 1 strval from dbo.SPLIT(string1,' '))) 
           ,6) theString //1st character of 1st word, followed by up to 5 characters of second word
         FROM table1
       ) X
    Where X.theString <> table2.someotherfield
      AND X.string1 = <whatever you need to join it to>