英文:
Method to adding an OR after a THEN in a CASE statement?
问题
我正在尝试验证一个列中的数据是否等于另一个列，并使用CASE语句来执行这些操作。我想知道是否有一种方法可以指定一个值可以等于它本身，或者等于NA。
一个表的示例：
列1 | 列2
A0 A0
A0 NA
B1 B1
B1 NA
C2 C2
C2 NA
我有以下代码：
select c1, c2, case when c1 = 'A0' then 'A0'
when c1 = 'B1' then 'B1'
when c1 = 'C2' then 'C2' end
from dbo
where case when c1 = 'A0' then 'A0'
when c1 = 'B1' then 'B1'
when c1 = 'C2' then 'C2' end != c2
这会告诉我在哪些行中c1不等于c2，但我获取的行都是NA。有没有可能让c1等于c2或NA？任何帮助都将不胜感激。
英文:
I'm trying to verify if data in one column equals another, and using CASE statements to do them, I was wondering if there's a way to specify that a value can equal the value itself, or NA.
An example of the table:
Column 1 | Column 2
A0 A0
A0 NA
B1 B1
B1 NA
C2 C2
C2 NA
The code that I have is
select c1, c2, case when c1 = 'A0' then 'A0'
when c1 = 'B1' then 'B1'
when c1 = 'C2' then 'C2' end
from dbo
where case when c1 = 'A0' then 'A0'
when c1 = 'B1' then 'B1'
when c1 = 'C2' then 'C2' end != c2
This would tell me where there are rows where c1 doesn't equal c2, but the rows that I fetch are all NA. Is it possible to account for c1 to equal either c2, or NA? Any help is appreciated.
答案1
得分: 2
不完全清楚您正在寻找什么，但我认为您想要：
where (c1 != c2 AND c2 != 'NA')
或
where not (c1 = c2 OR c2 = 'NA')
无论哪个对您来说更合乎逻辑。
我还看不出case语句对您有什么帮助 - 目前它只是重复了c1的值。
英文:
It's not completely clear what you're looking for, but I think you want:
where (c1 != c2 AND c2 != 'NA')
or
where not (c1 = c2 OR c2 = 'NA')
whichever makes the most logical sense to you.
I also don't see what the case statement does for you - right now it is just repeating the value of c1.
答案2
得分: 1
不需要使用case语句。
两者之间的全外连接将匹配两列，并返回仅其中一列具有值而另一列为null的记录。如果您只想返回两者匹配或C2为null的情况，请使用左连接。
英文:
No need for case statements.
Full outer join between the two will match the two columns and also return records where only one of the columns has values and the other one is null. If you want to return only cases where the two match OR C2 is null, then use left join.
答案3
得分: 0
你可以使用 row_number()：
选择 t.col1，当 seq = 1 时，将 t.col2 选为 col2，否则选为 'NA'。
来自 (select t.*，row_number() over (partition by col1 order by ?) as seq
from table t
) t;
? 表示排序列。
英文:
You can use row_number() :
select t.col1, case when seq = 1 then t.col2 else 'NA' end as col2
from (select t.*, row_number() over (partition by col1 order by ?) as seq
from table t
) t;
? indicates ordering columns.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论