SQL CASE查询 [英] SQL CASE Query

问题描述

在用SQL编写案例脚本时需要帮助，但是我是一个初学者，所以我要做很多工作，以减少现有脚本中的粘贴并更改变量.

我一直在尝试使它工作一段时间，但到目前为止仍未成功.

Hi,

I need help in writing a case script in SQL but I am a beginner so I am doing a lot of cutting an pasteing from existing scripts and changing the variables.

I have been trying to get this to work for a while but being unsuccessful so far.

SELECT OMCOMP,OMCUST, AccountFrom =
CASE
when OMCOMP = 'SG1'; and OMDIM1 = 1000 or OMCUST = IN(SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = SG1;) THEN '1'
when OMCOMP = 'SG1'; and OMDIM1 = 1234 or OMCUST = IN(SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = SG1;) THEN '2'
ELSE 'OTHERS'
END
FROM Finance_APAC.dbo.testFMOSAL

基本上我想创建一个表，该表在"1"或"2"或其他"中显示"AccountFrom".

在第一种情况下，如果OMCOMP为"SG1"且OMDIM1为1000，或者OMCOMP为"SG1"且OMCUST作为另一个表存在于一个国家为SG1的表中，则Accountfrom将等于1.

非常感谢任何人在我的问题上可以提供的任何专业知识.

Basically I want to create a table that would show ''AccountFrom'' either in ''1'' or ''2'' or '' Others''.

In the first instance, if OMCOMP is ''SG1'' and OMDIM1 is 1000 or if OMCOMP is ''SG1'' and OMCUST is exists as an account in another table where country is SG1 then Accountfrom will be equal to 1.

Thanks very much in advance for any expertise anyone can offer on my issue.

推荐答案

SELECT OMCOMP,OMCUST, AccountFrom =
CASE
when OMCOMP = 'SG1' and OMDIM1 = 1000 or OMCUST IN(SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = SG1) THEN '1'
when OMCOMP = 'SG1' and OMDIM1 = 1234 or OMCUST IN(SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = SG1) THEN '2'
ELSE 'OTHERS'
END
FROM Finance_APAC.dbo.testFMOSAL

尝试这个.我刚刚从 .. OMCUST = IN(SELEC ... 和一些不必要的分号中删除了``=''.

让我进一步了解它是否会导致任何错误.

Try this. I have just removed ''='' from ..OMCUST = IN ( SELEC... and some unnecessary semicolons.

Let me know further if it results in any error.

尝试

Try

SELECT OMCOMP,OMCUST, AccountFrom =CASE
when (OMCOMP = 'SG1' and OMDIM1 = 1000) or (OMCOMP = 'SG1' and OMCUST = IN(SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1')) THEN '1'
when (OMCOMP = 'SG1' and OMDIM1 = 1234) or (OMCOMP = 'SG1' or OMCUST = IN(SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1')) THEN '2'
ELSE 'OTHERS'
END
FROM Finance_APAC.dbo.testFMOSAL

SELECT OMCOMP,OMCUST,
CASE
when OMCOMP = 'SG1' and (OMDIM1 = 1000 or EXISTS(SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1' AND Account = OMCUST)) THEN '1'
when OMCOMP = 'SG1' and (OMDIM1 = 1234 or EXISTS(SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1' AND Account = OMCUST)) THEN '2'
ELSE 'OTHERS'
END AS AccountFrom 
FROM Finance_APAC.dbo.testFMOSAL

上面的代码应该做事.检查数据类型.

The above code should do the deed. Check data types.

这篇关于SQL CASE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！