英文:
How to create a new column in a select query with the column validation result?
问题
我有一张表，其中包含ColumnA、ColumnB、ColumnC，我想添加一个NewColumn进行一些验证。如果ColumnA、ColumnB、ColumnC都有值为"Yes"，那么NewColumn也应该显示"Yes"，否则显示"No"。
基本上，我想对现有的列进行一些验证，并将结果存储在NewColumn中。这可能吗？有人可以在这里提供帮助吗？谢谢！
| ColumnA | ColumnB | ColumnC | NewColumn |
|---|---|---|---|
| Yes | Yes | Yes | Yes |
| No | Yes | No | ColumnA和ColumnC为No |
| Yes | Yes | No | ColumnC为No |
| Yes | No | No | ColumnB和ColumnC为No |
英文:
I have a table with ColumnA, ColumnB, ColumnC and I wanted to have a NewColumn with some validation. If all the ColumnA, ColumnB, ColumnC have a value as "Yes" then NewColumn also should show "Yes" otherwise "No".
Basically, I want to do some validation on the existing Columns and store the result in the NewColumn. Is it possible? Someone can please help here? Thank you!
| ColumnA | ColumnB | ColumnC | NewColumn |
|---|---|---|---|
| Yes | Yes | Yes | Yes |
| No | Yes | No | ColumnA & ColumnC has No |
| Yes | Yes | No | ColumnC has No |
| Yes | No | No | ColumnB & ColumnC has No |
答案1
得分: 1
使用简单的case语句，有三个条件：
SELECT ColumnA, ColumnB, ColumnC,
CASE WHEN ColumnA = 'Yes' AND
ColumnB = 'Yes' AND
ColumnC = 'Yes'
THEN 'Yes'
ELSE 'No'
END NewColumn
FROM my_table
更新：
要获取列的名称，您需要连接这些列的名称，然后删除末尾的&。如何执行取决于DBMS。例如，在Oracle或Postgre中可以这样做：
SELECT ColumnA, ColumnB, ColumnC,
CASE WHEN ColumnA = 'Yes' AND
ColumnB = 'Yes' AND
ColumnC = 'Yes'
THEN 'Yes'
ELSE (
REGEXP_REPLACE(CASE WHEN ColumnA = 'Yes' THEN '' ELSE 'ColumnA & ' END ||
CASE WHEN ColumnB = 'Yes' THEN '' ELSE 'ColumnB & ' END ||
CASE WHEN ColumnC = 'Yes' THEN '' ELSE 'ColumnC & ' END,'..$','')) || 'has No'
)
END NewColumn
FROM my_table
在MySQL中，可以这样：
SELECT ColumnA, ColumnB, ColumnC,
CASE WHEN ColumnA = 'Yes' AND
ColumnB = 'Yes' AND
ColumnC = 'Yes'
THEN 'Yes'
ELSE (
CONCAT(
REGEXP_REPLACE(CONCAT(CASE WHEN ColumnA = 'Yes' THEN '' ELSE 'ColumnA & ' END,
CASE WHEN ColumnB = 'Yes' THEN '' ELSE 'ColumnB & ' END,
CASE WHEN ColumnC = 'Yes' THEN '' ELSE 'ColumnC & ' END),'..$',''),
'has No')
)
END NewColumn
FROM my_table
英文:
Use simple case with three conditions:
SELECT ColumnA, ColumnB, ColumnC,
case when ColumnA = 'Yes' and
ColumnB = 'Yes' and
ColumnC = 'Yes'
then 'Yes'
else 'No'
end NewColumn
from my_table
Update:
To get names of columns you'll need to concatenate names of those columns, and then remove & at the end. How you can do it depends on DBMS. For example in Oracle or Postgre it could be done this way:
SELECT ColumnA, ColumnB, ColumnC,
case when ColumnA = 'Yes' and
ColumnB = 'Yes' and
ColumnC = 'Yes'
then 'Yes'
else (
REGEXP_REPLACE(case when ColumnA = 'Yes' then '' else 'ColumnA & ' end ||
case when ColumnB = 'Yes' then '' else 'ColumnB & ' end ||
case when ColumnC = 'Yes' then '' else 'ColumnC & ' end,'..$','') || 'has No'
)
end NewColumn
from my_table
In MySQL it would be
SELECT ColumnA, ColumnB, ColumnC,
case when ColumnA = 'Yes' and
ColumnB = 'Yes' and
ColumnC = 'Yes'
then 'Yes'
else (
CONCAT(
REGEXP_REPLACE(CONCAT(case when ColumnA = 'Yes' then '' else 'ColumnA & ' end,
case when ColumnB = 'Yes' then '' else 'ColumnB & ' end,
case when ColumnC = 'Yes' then '' else 'ColumnC & ' end),'..$',''),
'has No')
)
end NewColumn
from my_table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论