选择里面 CASE THEN [英] Select inside CASE THEN

问题描述

我需要选择生效日期小于今天的项目费率或班次费率.

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)

请注意，此代码片段是较大存储过程的一部分，因此必须在 CASE 语句中.

Please note that this code snippet is part of a larger stored proc and thus it must be within a CASE statement.

推荐答案

子查询需要括号:

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;

这篇关于选择里面 CASE THEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！