gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-04 23:45:35 25 4
gpt4 key购买 nike

长话短说,我有一个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

最佳答案

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

CASE ... < (Select AttFloorScore from tblAvs1Scoring)

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

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

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

关于sql - 子查询返回多个值,但我使用 "IN"作为我的运算符,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51252442/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com