sql - 子查询返回多个值，但我使用 "IN"作为我的运算符

Question

长话短说，我有一个Crosswalk 表，它有一个列名MgrFilterRacf。表中有一行用于在几个查询中引用各种内容，这种方法对我来说效果很好。但是，当我不得不引用多个值时，它会出现以下错误。我真的不知道为什么它不起作用。我看到了大量关于此的主题/帖子，但大多数都是声明使用 IN 运算符的解决方案，我已经这样做了。有点恼火的是，当我只调用一个值并删除人行横道表上的第二行时，无论使用“In”还是“=”，它都有效。

引用错误:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

人行横道表格式:

Id  | AttCeilingScore | AttFloorScore  | MgrFilterRacf    
----+-----------------+----------------+---------------
 1  |    100          |      75        |    Value1    
 2  |    NULL         |     NULL       |    Value2

查询:

--\\*Perform calculation For Attendance Score Sa*\\
Select
    MgrRacf,
    MgrName,
    Ctc.racf as EmpRacf,
    Ctc.EmpName,
    Case 
       when AttCntSegment is null then 0
       else AttCntSegment 
    end as AttCntSegment,
    Case 
       when AttSumDuration is null then 0
       else AttSumDuration 
    end as AttSumDuration,
    case
       when AttCntSegment > 12 
          then (Select AttFloorScore from tblAvs1Scoring)
       when AttCntSegment is null 
          then (Select AttCeilingScore from tblAvs1Scoring)
       when 100 - ((AttCntSegment) * 2 + PercentReduction) < (Select AttFloorScore from tblAvs1Scoring) 
          then (Select AttFloorScore from tblAvs1Scoring)
       else 100 - ((AttCntSegment)*2+PercentReduction)
    end As AttScore,
    Case
       when AttCntSegment is null then 100
       else 100 - ((AttCntSegment)*2+PercentReduction) 
    end as AttScoreRaw,
    'RollSixSum' as ReportTag
From
    (--\\*Get Total Occurrences from Rolling 6 months per advocate*\\
     SELECT 
         EMP_ID,
         COUNT(SEG_CODE) AS AttCntSegment, 
         SUM(DURATION) AS AttSumDuration
     FROM   
         tblAttendance AS Att
     WHERE  
         (START_DATE >= Getdate() - 180) 
         AND (SEG_CODE NOT IN ('FLEX2', 'FMLA'))
         AND (DURATION > 7)
         AND START_DATE IS NOT NULL
     GROUP BY 
         EMP_ID) As Totals
INNER JOIN 
    tblCrosswalkAttendanceTime AS Time ON AttSumDuration BETWEEN Time.BegTotalTime AND Time.EndTotalTime
RIGHT JOIN 
    tblContactListFull AS Ctc ON Ctc.employeeID = Totals.EMP_ID
WHERE
    --Ctc.Mgr2racf IN ('Value1','Value2')   --This works
    Ctc.Mgr2racf IN (SELECT MgrFilterRacf FROM tblAvs1Scoring) --This returns the same 2 values but doesn't work, note works with only 1 value present
    AND (title LIKE '%IV%' OR Title LIKE '%III%' OR title LIKE '%Cust Relat%')  --Going to apply same logic here once I have a solution
    AND employeestatus2  <> 'Inactive'

具体违规代码行:

 Ctc.Mgr2racf IN (Select MgrFilterRacf from tblAvs1Scoring) --This returns the same 2 values but doesnt work, note works with only 1 value present
        AND (title LIKE '%IV%' or Title like '%III%' or title LIKE '%Cust Relat%')  --Going to apply same logic here once I have a solution

Answer 1

这不是 IN导致错误的子句，因为 IN不必只返回 1 个值。相反，它可能在这里:

CASE ... < (Select AttFloorScore from tblAvs1Scoring)

尝试 select count(AttFloorScore) from tblAvs1Scoring看看它是否 > 1。我确定是。

这可以通过适合您的数据的任何方法来缓解。

1. 使用 TOP 1
2. 使用类似 MAX() 的聚合
3. 关联子查询以限制返回的行。
4. 使用适当的 WHERE条款