SQL INNER加入案例说明
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15786611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Case Statement on INNER Join
提问by Richard C
Trying to use CASE statements on a inner join and all I'm getting is syntax errors, anyone got any advice on this?
尝试在内部联接上使用 CASE 语句，而我得到的只是语法错误，有人对此有什么建议吗？
Here is the code
这是代码
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
This is the first time i've even worked with case statements let alone combining them with a inner join so i'm sorry if i've totally messed up.
这是我第一次使用 case 语句，更不用说将它们与内部连接相结合，所以如果我完全搞砸了，我很抱歉。
The syntax errors im getting are:
我得到的语法错误是：
Incorrect syntax on the = and WHEN here
此处 = 和 WHEN 的语法不正确
THEN Call_Type_ID = 10
WHEN
And incorrect syntax expecting CONVERSION on the GROUP BY
并且在 GROUP BY 上期望 CONVERSION 的语法不正确
回答by Werner Waage
It seems like you are trying to create Where-clauses in the case, but you should instead compare the result of the case against Call_Type_ID(or any other field you want) as in the example i wrote below Hope it helps!
似乎您正在尝试在案例中创建 Where 子句，但您应该将案例的结果与 Call_Type_ID（或您想要的任何其他字段）进行比较，就像我在下面写的示例中那样希望它有所帮助！
Also sometimes i use brackets over my casing to make it easier to see where they start and stop.
此外，有时我会在外壳上使用括号，以便更容易查看它们的开始和停止位置。
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.
回答by Yogesh Patil
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
from emp inner join dept on emp.deptno=dept.deptno ;
从 emp 内连接部 emp.deptno=dept.deptno ;
................................................................................
………………………………………………………………………………………………………………………………………………………… …………………………………………………………………………………………………………………………………………………………………………
I am considering here table as oracle by default table.
我将这里的表视为默认表。
