sql-server - SQL 在 where 子句中使用 CASE

Question

这是一个表格示例:

     acIdent   |  acSubject          | acCode
     200.2.013 |                     | F-202
     200.2.013 | Minsk traktor works | F202.GRS

这就是我尝试做的:

     declare @subj varchar(30)

     set @subj = 'Minsk traktor works'

     select acIdent, acSubject, acCode
     from tHE_SetItemExtItemSubj where acIdent = '200.2.013' and
     acSubject = (
            case 
            when @subj = acSubject then @subj
            else '' end
         )

我的目标是只获得一项记录。我的示例返回两条记录。当表中存在@subj时返回acCode - F202.GRS，否则返回F-202。

请帮忙

Answer 1

如果我理解正确的话，你正在寻找这样的东西:

 declare @subj varchar(30)

 set @subj = 'Minsk traktor works'

 select top(1) acIdent, acSubject, acCode
 from tHE_SetItemExtItemSubj 
 where acIdent = '200.2.013' 
   and (acSubject = @subj or acSubject = '')
 order by len(acSubject) desc