oracle 使用 case when 语句创建视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8180394/
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
Creation of view with case when statement
提问by JLearner
Please refer to my example below for clearer understanding of what i'm doing.
请参考我下面的示例，以便更清楚地了解我在做什么。
Example:
例子：
Create View v AS
Select T.*, S.Name, Case When T.TESTDATE = S.STUDYDATE
Then 'Yes' else 'No' END AS TakenTest
From Test T, Student S
Where T.TESTPAPERID = '12345'
And T.StudentNo = S.StudentNo;
It create the view successfully. However, it populate duplicate rows with same values like:
它成功创建了视图。但是，它使用相同的值填充重复的行，例如：
TESTPAPERID StudentNo Name TakenTest
12345 6437 John Yes
12345 6437 John No
How can i resolve it as i already define that if T.TESTDATE = S.STUDYDATE then show yes. Otherwise no. And not populating the same values out.
我如何解决它，因为我已经定义了如果 T.TESTDATE = S.STUDYDATE 然后显示是。否则没有。并且不填充相同的值。
Thanks
谢谢
回答by Bohemian
To have only one row, use this:
要只有一行，请使用以下命令：
MAX(Case When T.TESTDATE = S.STUDYDATE Then 'Yes' else 'No' END)
...
GROUP BY <all other columns, which you haven't shown>
Also, this part is poorly coded:
此外，这部分编码很差：
T.TESTPAPERID = (Select TESTPAPERID From Student Where TESTPAPERID='12345')
because it is IDENTICAL to:
因为它是相同的：
T.TESTPAPERID = '12345'
