用例
CASE可以与一起使用,SUM以仅返回与预定义条件匹配的那些项目的计数。(这类似于COUNTIFExcel中的。)
诀窍是返回表示匹配项的二进制结果,因此可以将为匹配项返回的“ 1”相加,得出匹配总数的计数。
给定此表ItemSales,假设您要了解已归类为“昂贵”商品的总数:
ID | ItemId | 价钱 | 定价 |
---|---|---|---|
1 | 100 | 34.5 | 昂贵 |
2 | 145 | 2.3 | 便宜的 |
3 | 100 | 34.5 | 昂贵 |
4 | 100 | 34.5 | 昂贵 |
5 | 145 | 10 | 买得起 |
询问
SELECT COUNT(Id) AS ItemsCount, SUM ( CASE WHEN PriceRating = 'Expensive' THEN 1 ELSE 0 END ) AS ExpensiveItemsCount FROM ItemSales
结果:
ItemsCount | ExpensiveItemsCount |
---|---|
5 | 3 |
选择:
SELECT COUNT(Id) as ItemsCount, SUM ( CASE PriceRating WHEN 'Expensive' THEN 1 ELSE 0 END ) AS ExpensiveItemsCount FROM ItemSales