sql - Case Statements in SQL - Nature of the Process

Question

I am writing a case statement in SQL to determine if an employee is a veteran or veteran spouse. There are three fields that contain the data and the logic would be:

1) If any of the three fields indicates veteran, the person is a veteran 2) If none indicate veteran but the last one indicates spouse, the person is a spouse

I have written a case statement that seems to work:

CASE
WHEN Flag1 = 'Vet' THEN 'Veteran'
WHEN Flag2 = 'Vet' THEN 'Veteran'
WHEN Flag3 = 'Vet' THEN 'Veteran'
WHEN Flag3 = 'Spouse' THEN 'Veteran Spouse'
ELSE 'Non Veteran/Spouse'
END

I am wondering if this works because there are no scenarios in my data that are causing to fail, not because it is written correctly. For example, I have no data where one of the first 2 flags says vet and flag 3 is spouse (which I would expect to show as a vet). What I want to know is: when the case statement hits it's first when that is positive, does it take action on the record and move to the next row or does it look at the next when and take action if those conditions are met as well changing the response (example: if flag1 is vet the case statement output is Veteran. If the same row had the word spouse in flag3 would the record be changed to Veteran Spouse?)

标签： sqloraclecase

Answer 1

你所拥有的是正确的。数据库将按顺序评估WHEN条件并使用匹配的第一个条件。

为了好玩，你也可以这样写：

CASE WHEN 'Vet' IN (Flag1, Flag2, Flag3) THEN 'Veteran'
     WHEN Flag3 = 'Spouse' THEN 'Veteran Spouse'
     ELSE 'Non Veteran/Spouse'
END

如果这些值位于您可以加入的小型查找表中，那就更好了。