SQL中HAVING子句中的CASE语句

Question

我能在SQL Server 2005的HAVING语句中使用case语句吗？

以下是我的HAVING语句，它给出了一个语法错误。

@CLIENTPK_NEW IS NULL OR 
    (
        CLIENT.OH_PK = @CLIENTPK_NEW and 
        CASE WHEN @RelatedOrgs <> '11' then CLIENT.OH_PK= @CLIENTPK_NEW
        ELSE CLIENT.OH_PK in (
            SELECT dbo.OrgHeader.OH_PK FROM dbo.OrgHeader WITH (NOLOCK) INNER JOIN
            dbo.OrgRelatedParty WITH (NOLOCK) ON dbo.OrgHeader.OH_PK = dbo.OrgRelatedParty.PR_OH_Parent INNER JOIN
            dbo.OrgHeader AS OrgHeader_1 WITH (NOLOCK) ON dbo.OrgRelatedParty.PR_OH_RelatedParty = OrgHeader_1.OH_PK
            where OrgHeader_1.OH_PK = @CLIENTPK_NEW
        ) 
        END 
    )
}
AND (@CGNEEPK IS NULL OR CGNEE.OH_PK = @CGNEEPK) AND    
part.OP_RH_NKCommodityCode = @type 

感谢您，Amit。

谢谢，

- Amit

Answer 1

示例（来自此处）：

USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 
    ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M' 
        THEN ph1.Rate 
        ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F' 
        THEN ph1.Rate  
        ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

- Mitch Wheat

---

谢谢。我已经添加了一段代码，但它给我语法错误，我无法弄清楚问题出在哪里。请给予建议。 - Amit

Answer 2

是的，那是有效的语法。然而，文本、图像和ntext数据类型不能在HAVING子句中使用。

更新：您更新的示例没有意义。要么CLIENT.OH_PK=@CLIENTPK_NEW，要么不是，语句的其余部分都是无关紧要的，除非您使用OR。也许您可以解释一下业务逻辑？

- D'Arcy Rittich

Answer 3

这里有另一个例子：这里..

SELECT EmployeeName ,Country,CompanyPlant,Gender, Total=MAX(PayScale)
FROM Employee
GROUP BY EmployeeName ,Country,CompanyPlant,Gender
HAVING (MAX(CASE WHEN Gender = 'Male'
 THEN PayScale
 ELSE NULL END) > 150.00
 OR MAX(CASE WHEN Gender = 'Female'
 THEN PayScale
 ELSE NULL END) > 180.00)

- nagnath