在Satement的情况下使用SubQuery(Using SubQuery in case Satement)

 我的任务是创建一个查询来从今天的日期提取员工信息。 现在我有测试数据，为下面的每个条件返回一条记录。 如果子查询返回多条记录怎么办？ 有没有更好的方法来写这个？ 
 
 查询Employee_Master。 
 
 我需要显示名称，ID，类型和日期。 
 
 Type将成为case语句创建的列。 
 
 当Start_Date = Today_Date时，Start_Date = Today_Date'TERMINATED'当'Start_Date <> Today_Date和Terminate_Date <> Today_Date时，当Start_Date = Today_Date'TERMINATED'时，今日的employee_reference表中存在相同的记录。 如果用户是'EXTENDED'并且存在于Name_Change表中，则“NAME CHANGED” 
 
SELECT Name, ID, Date,
Type = CASE
WHEN Start_Date = Today_Date THEN 'NEW'
WHEN Terminate_Date = Today_Date THEN 'TERMINATED'
WHEN ID = (SELECT A.ID FROM Employee_Master AS A
INNER JOIN Employee_Reference AS B on B.ID = A.ID
WHERE DATE = GETDATE()) THEN 'EXTENDED'
WHEN ID = (SELECT A.ID FROM Employee_Master AS A
INNER JOIN Name_Change AS B ON B.ID = A.ID 
WHERE Create_Date = GETDATE()) THEN 'Name Changed'
END
FROM Employee_Master
WHERE Today_Date = GETDATE()

I'm tasked with creating a query to pull Employee information from today's date. Right now I have test data that returns one record for each of the conditions below. What if the subquery returns multiple records? Is there a better way to write this?
 
Query Employee_Master. 
 
I need to display the Name, ID, Type, and Date.
 
Type is going to be a column created by a case statement. 
 
'NEW' when Start_Date = Today_Date 'TERMINATED' when Terminate_Date = Today_Date 'EXTENDED' when Start_Date<>Today_Date and Terminate_Date<>Today_Date and the same record exists in employee_reference table for today's date. 'NAME CHANGED' if the user is 'EXTENDED' and exists in Name_Change table
 
SELECT Name, ID, Date,
Type = CASE
WHEN Start_Date = Today_Date THEN 'NEW'
WHEN Terminate_Date = Today_Date THEN 'TERMINATED'
WHEN ID = (SELECT A.ID FROM Employee_Master AS A
INNER JOIN Employee_Reference AS B on B.ID = A.ID
WHERE DATE = GETDATE()) THEN 'EXTENDED'
WHEN ID = (SELECT A.ID FROM Employee_Master AS A
INNER JOIN Name_Change AS B ON B.ID = A.ID 
WHERE Create_Date = GETDATE()) THEN 'Name Changed'
END
FROM Employee_Master
WHERE Today_Date = GETDATE()

原文：https://stackoverflow.com/questions/31571051