INNER JOIN中的CASE语句

Question

尝试在内部连接上使用CASE语句，但我一直收到语法错误，有人能给些建议吗？

以下是代码：

 SELECT  
    Call_type_ID,
    SUM (staging.dbo.outgoing_measure.ring_time) AS Ring_Time,
    SUM (staging.dbo.outgoing_measure.hold_time) As Hold_Time,
    SUM (staging.dbo.outgoing_measure.talk_time) AS Talk_Time,
    SUM (staging.dbo.outgoing_measure.acw_time) AS ACW_Time,
    COUNT(*) CallCount
FROM outgoing_measure

INNER JOIN datamartend.dbo.Call_Type_Dim ON 
CASE 
WHEN 
CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned  IS NULL
AND AnsTime > 0
AND CallState IS NULL
THEN Call_Type_ID = 10
WHEN
CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned  IS NULL
AND AnsTime > 0  
AND CallState = 1
THEN call_Type_id = 11
WHEN 
CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned = 1
AND AnsTime IS NULL
AND CallState IS NULL
THEN call_type_ID = 12
ELSE call_type_id = 1
END

Group by call_Type_id

这是我第一次使用case语句，更不用说将它们与内部连接结合使用了，所以如果我搞砸了，我很抱歉。

我遇到的语法错误是：

= 和 WHEN 这里的语法不正确。

THEN Call_Type_ID = 10
WHEN

在 GROUP BY 子句中出现了期望进行转换的不正确语法

- Richard C

Answer 1

看起来你正试图在CASE中创建Where子句，但你应该将CASE的结果与Call_Type_ID（或任何其他你想要的字段）进行比较，就像我在下面写的例子一样。

希望这可以帮到你！

有时我还会使用括号来标记我的CASE语句，以便更容易地看出它们开始和结束的位置。

INNER JOIN datamartend.dbo.Call_Type_Dim ON 
(CASE 
 WHEN  CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned  IS NULL
     AND AnsTime > 0
     AND CallState IS NULL
     THEN 10
WHEN CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned  IS NULL
     AND AnsTime > 0  
     AND CallState = 1
     THEN 11
WHEN 
     CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned = 1
     AND AnsTime IS NULL
     AND CallState IS NULL
     THEN 12
ELSE 1
END) = Call_Type_ID  -- Insert something here to join on.

- WernerW

---

我认为你可能是对的，就像我说的，我对整个概念都很新。谢谢你的建议。 - Richard C

Answer 2

select * from emp;
select * from dept;

...........................................................................

select Ename,Job,dname,
Case job
when 'Clerk' then 'C'
when 'Salesman' then 'S'
when 'Manager' then 'M'
when 'Analyst' then 'A'
else 'Other'
end  as Demo

从 emp 内连接 dept on emp.deptno=dept.deptno ;

................................................................................

我在这里将表格默认为Oracle表格。

- Yogesh Patil