accept（）函数如何工作？(How does the accept() function work?)

 我不完全确定你的所有日期都来自子选择，因为它们没有别名，但概念上我会尝试： 
 
SELECT 
    Name, 
    ID, 
    Date,
    Type = CASE
            WHEN Start_Date = Today_Date THEN 'NEW'
            WHEN Terminate_Date = Today_Date THEN 'TERMINATED'
            WHEN (SELECT MAX(DATE)
                    FROM Employee_Reference ER on ER.ID = EM.ID) = GETDATE() THEN 'EXTENDED'
            WHEN ID = (SELECT MAX(Create_Date)
                        FROM Name_Change AS NC ON NC.ID = EM.ID) = GETDATE() THEN 'Name Changed'
           END
FROM Employee_Master EM
WHERE Today_Date = GETDATE()
 
 从本质上讲，这消除了employee_master中额外选择的ID，因为您已经从中进行选择，并从子选择中的目标表中选择最大日期。 你只会以这种方式得到一个，如果它符合今天的日期，那么它将满足案例陈述。 
 
 请注意，上面假设您的原始查询获得了整个人口。 但是，我怀疑您可能希望在FROM语句中实际包含employee_reference和name_change作为外连接，然后从WHERE语句中的当前日期过滤到所有内容 - 类似于： 
 
SELECT 
    Name, 
    ID, 
    Date,
    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 AS Type
FROM Employee_Master EM
    LEFT OUTER JOIN Employee_Reference ER
        ON ER.ID = EM.ID
    LEFT OUTER JOIN Name_Change NC
        ON NC.ID = EM.ID
WHERE 
 Today_Date = GETDATE()
 OR DATE = GETDATE() --note: you'll need to alias date since there appears to be more than one column named this
 OR Create_Date = GETDATE()

I'm not totally sure where all your dates are coming from in the subselects since they aren't aliased, but here is conceptually what I would try:
 
SELECT 
    Name, 
    ID, 
    Date,
    Type = CASE
            WHEN Start_Date = Today_Date THEN 'NEW'
            WHEN Terminate_Date = Today_Date THEN 'TERMINATED'
            WHEN (SELECT MAX(DATE)
                    FROM Employee_Reference ER on ER.ID = EM.ID) = GETDATE() THEN 'EXTENDED'
            WHEN ID = (SELECT MAX(Create_Date)
                        FROM Name_Change AS NC ON NC.ID = EM.ID) = GETDATE() THEN 'Name Changed'
           END
FROM Employee_Master EM
WHERE Today_Date = GETDATE()
 
Essentially, this eliminates the extra selection of ID from employee_master, since you are already selecting from that, and selects the max date from the table targeted in the sub-select. You'll only get one back that way, and if it matches today's date then it will satisfy the case statement. 
 
Note that the above assumes your original query gets the entire population. However, I suspect you might want to actually include employee_reference and name_change as outer joins in your FROM statement and then filter down to everything from the current date in your WHERE statement - something like: 
 
SELECT 
    Name, 
    ID, 
    Date,
    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 AS Type
FROM Employee_Master EM
    LEFT OUTER JOIN Employee_Reference ER
        ON ER.ID = EM.ID
    LEFT OUTER JOIN Name_Change NC
        ON NC.ID = EM.ID
WHERE 
 Today_Date = GETDATE()
 OR DATE = GETDATE() --note: you'll need to alias date since there appears to be more than one column named this
 OR Create_Date = GETDATE()