SQL sql中have子句中的case语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4205012/
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
case statement in having clause in sql
提问by Amit
Can I have a case statement in a HAVINGclause in SQL server 2005?
我可以HAVING在 SQL Server 2005的子句中使用 case 语句吗？
Below is my HAVINGstatement. It is giving me a syntax error.
下面是我的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
Thanks,
谢谢，
Amit
阿米特
回答by Mitch Wheat
Example (from here):
示例（来自此处）：
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;
回答by RedFilter
Yes, that is valid syntax. However, the text, image, and ntext data types cannot be used in a HAVINGclause.
是的，这是有效的语法。但是，不能在HAVING子句中使用 text、image 和 ntext 数据类型。
Update:Your updated example does not make sense. Either CLIENT.OH_PK=@CLIENTPK_NEWor it doesn't, the rest of the statement is irrelevent, unless you use an OR. Perhaps you can explain the busingess logic?
更新：您更新的示例没有意义。不管CLIENT.OH_PK=@CLIENTPK_NEW是不是，语句的其余部分都是无关紧要的，除非您使用OR. 也许您可以解释业务逻辑？
回答by nagnath
another example from here..
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)
