英文:
If two column has Yes then new column should be Yes in SQL
问题
I want to create new column Res which should be Y if any two columns out of A B C is Y else should be N
我想创建一个新的列"Res"，如果A、B、C中的任意两列为Y，则该列应为Y，否则应为N。
英文:
I have below table:
| Name | A | B | C |
|---|---|---|---|
| X | Y | N | Y |
| Z | N | Y | N |
I want to create new column Res which should be Y if any two columns out of A B C is Y else should be N
| Name | A | B | C | Res |
|---|---|---|---|---|
| X | Y | N | Y | Y |
| Z | N | Y | N | N |
PS: I have more than three column to check on
答案1
得分: 1
以下是翻译好的内容：
你可以尝试：
SELECT Name, A, B, C,
CASE
WHEN 'Y' IN (A,B) OR 'Y' IN (A,C) OR 'Y' IN (B,C) THEN 'Y'
ELSE 'N'
END AS Res
FROM [table_name];
如果你有许多列并且可以使用 UNPIVOT，你可以像这样做：
SELECT u.Name
FROM
(
SELECT Name, A, B, C
FROM your_table_name
) DS
UNPIVOT
(
Res FOR Col IN (A, B, C)
) UNPVT
WHERE UNPVT.Res = 'Y'
GROUP BY UNPVT.Name
HAVING COUNT(DISTINCT u.Col) >= 2;
英文:
You can try:
SELECT Name, A, B, C,
CASE
WHEN 'Y' IN (A,B) OR 'Y' IN (A,C) OR 'Y' IN (B,C) THEN 'Y'
ELSE 'N'
END AS Res
FROM [table_name];
If you have many columns and can use UNPIVOT you can do something like this:
SELECT u.Name
FROM
(
SELECT Name, A, B, C
FROM your_table_name
) DS
UNPIVOT
(
Res FOR Col IN (A, B, C)
) UNPVT
WHERE UNPVT.Res = 'Y'
GROUP BY UNPVT.Name
HAVING COUNT(DISTINCT u.Col) >= 2;
答案2
得分: 0
SELECT *，CASE WHEN A = "Y" AND B = "Y" THEN "Y" WHEN B = "Y" AND C = "Y" THEN "Y" WHEN A = "Y" AND C = "Y" THEN "Y" ELSE "N" END AS Res FROM your_table
英文:
SELECT *, case when A = "Y" AND B = "Y" then "Y"
when B = "Y" AND C = "Y" then "Y"
when A = "Y" AND C = "Y" then "Y"
else "N"
end as Res
FROM your_table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论