SQL 选择里面 CASE THEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32203219/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 03:59:23 来源:igfitidea点击:
Select inside CASE THEN
提问by artknight
I need to select the project rate or shift rate that has the effective date less than today.
我需要选择生效日期小于今天的项目费率或班次费率。
SELECT
CASE
WHEN ISNULL(s.rate,0) = 0
THEN SELECT TOP 1 pr.rate FROM ProjectRates pr WHERE (pr.projectID = p.ID) AND (pr.effectiveDate < GETDATE()) ORDER BY pr.effectiveDate DESC
--p.rate
ELSE SELECT TOP 1 sr.rate FROM ShiftRates sr WHERE (sr.shiftID = s.ID) AND (sr.effectiveDate < GETDATE()) ORDER BY pr.effectiveDate DESC
--s.rate
END AS rate
FROM Projects p
INNER JOIN Shifts s ON (p.ID = s.projectID)
WHERE (p.ID = @projectID)
Please note that this code snippet is part of a larger stored proc and thus it must be within a CASE statement.
请注意，此代码片段是更大的存储过程的一部分，因此它必须在 CASE 语句中。
回答by Gordon Linoff
Subqueries need parentheses:
子查询需要括号：
SELECT (CASE WHEN ISNULL(s.rate, 0) = 0
THEN (SELECT TOP 1 pr.rate
FROM ProjectRates pr
WHERE (pr.projectID = p.ID) AND (pr.effectiveDate < GETDATE())
ORDER BY pr.effectiveDate DESC
)
ELSE (SELECT TOP 1 sr.rate
FROM ShiftRates sr
WHERE (sr.shiftID = s.ID) AND (sr.effectiveDate < GETDATE())
ORDER BY pr.effectiveDate DESC
) --s.rate
END) AS rate
FROM Projects p INNER JOIN
Shifts s
ON p.ID = s.projectID
WHERE p.ID = @projectID;
