SQL 使用 Case 语句时 MIN 和 MAX 的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8982178/
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
Issues with MIN & MAX when using Case statement
提问by user1166296
I am trying to generate a summary report using various aggregate functions: MIN, MAX, SUM, etc. The issue I have is when I try to get a MIN and MAX of a field when I am also using the case statement. I am unable to get the MIN value of a field when I am using the case statement. I can best explain it with sample data and the sql statement:
我正在尝试使用各种聚合函数生成摘要报告：MIN、MAX、SUM 等。我遇到的问题是当我还使用 case 语句时尝试获取字段的 MIN 和 MAX。使用 case 语句时，我无法获取字段的 MIN 值。我可以用示例数据和 sql 语句最好地解释它：
Fields: AccountNumber, Symbol, TradeDate, TransactionType, Price, Quantity, Amount Table: Trades
字段：AccountNumber、Symbol、TradeDate、TransactionType、Price、Quantity、Amount 表：交易
AccountNumber, Symbol, TradeDate, TransactionType, Price, Quantity, Amount
123,"XYZ",1/2/2011,"Buy",15,100,1500
123,"XYZ",1/2/2011,"Buy",10,50,500
123,"XYZ",1/2/2011,"Sell",20,100,2000
456,"ABC",1/3/2011,"Buy",10,20,200
456,"ABC",1/3/2011,"Buy",15,30,450
789,"DEF",1/4/2011,"Sell",30,100,3000
Query:
询问：
SELECT AccountNumber,
Symbol,
SUM(case when TransactionType = "Buy" then 1 else 0) as TotalBuys,
SUM(case when TransactionType = "Sell" then 1 else 0) as TotalSells,
MIN(case when TransactionType = "Buy" then Price else 0) as MinBuy,
MAX(case when TransactionType = "Buy" then Price else 0) as MaxBuy,
MIN(case when TransactionType = "Sell" then Price else 0) as MinSell,
MAX(case when TransactionType = "Sell" then Price else 0) as MaxSell,
MIN(Price) as MinPrice,
MAX(Price) as MaxPrice
FROM Trades
Group By AccountNumber, Symbol
What I am expecting is the following results:
我期待的是以下结果：
AccountNumber, Symbol, TotalBuys, TotalSells, MinBuy, MaxBuy, MinSell, MaxSell, MinPrice, MaxPrice
123,"XYZ",2,1,10,15,20,20,10,20
456,"ABC",2,0,10,15,0,0,10,15
789,"DEF",0,1,0,0,30,30,30,30
However, I am getting the following results:
但是，我得到以下结果：
AccountNumber, Symbol, TotalBuys, TotalSells, MinBuy, MaxBuy, MinSell, MaxSell, MinPrice, MaxPrice
123,"XYZ",2,1,**0**,15,**0**,20,**0**,20
456,"ABC",2,0,10,15,0,0,10,15
789,"DEF",0,1,0,0,30,30,30,30
When there are two different TransactionTypes for each grouping, the Min fields (MinBuy,MinSell, and MinPrice) are coming out as 0 as opposed to what is expected. What am I doing wrong on the sql statement? Is there another way to get the desired results?
当每个分组有两个不同的 TransactionType 时，Min 字段（MinBuy、MinSell 和 MinPrice）将显示为 0，而不是预期值。我在 sql 语句上做错了什么？有没有其他方法可以得到想要的结果？
回答by dani herrera
Min between 0 and a positive number is 0, you should change:
0 和正数之间的最小值为 0，您应该更改：
MIN(case when TransactionType = "Buy" then Price else 0)
by
经过
MIN(case when TransactionType = "Buy" then Price else Null)
Null don't compute in an aggregation function.
Null 不在聚合函数中计算。
Thats all.
就这样。
Edited 6 years later:
6年后编辑：
As P5Codersays, it is enough without elseclause, also I guess the endis mandatory on some database brands. Here it is:
正如P5Coder所说，没有else子句就足够了，我想这end对于某些数据库品牌来说是强制性的。这里是：
MIN(case when TransactionType = "Buy" then Price end)
