SQL 聚合函数和 CASE WHEN 多条件使用怎么写？-SQL-PHP中文网

答案：SQL中通过在聚合函数内嵌套CASE WHEN实现条件聚合，可在一个查询中对不同条件的数据分别计数、求和或计算平均值等。例如统计每个客户的总订单数、已完成订单金额、待处理订单数及平均完成订单金额，相比多次查询或子查询更高效灵活。解析：COUNT(order_id)统计总订单；SUM(CASE WHEN status='completed' THEN amount ELSE 0 END)累加已完成订单金额，ELSE 0确保未完成订单贡献为零；COUNT(CASE WHEN status='pending' THEN 1 END)利用COUNT忽略NULL的特性，仅统计待处理订单；AVG(CASE WHEN status='completed' THEN amount END)自动忽略NULL，只计算已完成订单的平均金额。与WHERE子句只能前置过滤整行不同，CASE WHEN在聚合时按行进行条件判断，允许同一行数据参与多个条件分支的聚合，实现“横向多维分析”。常见陷阱包括NULL处理不当导致结果偏差（如AVG中误用ELSE 0）、复杂CASE逻辑影响性能、数据类型不匹配引发隐式转换错误。此外，MAX/MIN可用于提取符合条件的极值，STRING_AGG结合CASE可拼接特定状态的字符串，VAR/STDEV类函数支持条件统计分析，APPROX_COUNT_DISTINCT适用于大数据下的条件去重估算。该技术提升了SQL的表达能力，是构建复杂报表

sql 聚合函数和 case when 多条件使用怎么写？

SQL聚合函数与

CASE WHEN

登录后复制

的结合使用，核心在于将条件判断逻辑内嵌到聚合函数的作用域内，这样就能在同一查询结果中，根据不同的业务条件对数据进行分类计数、求和或计算其他统计量。这比单纯用

WHERE

登录后复制

子句过滤后再聚合要灵活得多，因为它允许你同时看到满足不同条件的数据聚合结果，而无需多次查询或复杂的子查询。

解决方案

在SQL中，

CASE WHEN

登录后复制

语句的强大之处在于它能根据特定条件返回不同的值。当这个能力与聚合函数（如

COUNT

登录后复制

SUM

登录后复制

AVG

登录后复制

MAX

登录后复制

MIN

登录后复制

等）结合时，我们便能实现极其灵活且富有洞察力的条件聚合。这就像给聚合函数装上了“智能筛选器”，让它只关注满足特定条件的数据片段。

举个例子，假设我们有一个

orders

登录后复制

表，包含

order_id

登录后复制

customer_id

登录后复制

amount

登录后复制

status

登录后复制

（订单状态，如'pending', 'completed', 'cancelled'）和

order_date

登录后复制

等字段。我们想在一个查询中，统计每个客户的总订单数、已完成订单的总金额，以及待处理订单的数量。

SELECT
    customer_id,
    COUNT(order_id) AS total_orders,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_sales_amount,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders_count,
    AVG(CASE WHEN status = 'completed' THEN amount END) AS avg_completed_order_amount -- 注意这里对NULL的处理
FROM
    orders
GROUP BY
    customer_id
ORDER BY
    customer_id;

登录后复制

解析：

```
COUNT(order_id)
```
登录后复制
：这是最直接的，统计每个客户的所有订单。
```
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END)
```
登录后复制
：这里是关键。对于每一行，如果
```
status
```
登录后复制
是'completed'，
```
CASE WHEN
```
登录后复制
就返回
```
amount
```
登录后复制
的值；否则，返回
```
0
```
登录后复制
。
```
SUM
```
登录后复制
函数接着会对这些返回的值进行求和。
```
ELSE 0
```
登录后复制
在这里很重要，它确保未完成的订单不会影响总金额，如果写成
```
ELSE NULL
```
登录后复制
，
```
SUM
```
登录后复制
函数会忽略
```
NULL
```
登录后复制
值，可能导致结果不符合预期（除非你确实想忽略）。
```
COUNT(CASE WHEN status = 'pending' THEN 1 END)
```
登录后复制
：这个模式常用于条件计数。如果
```
status
```
登录后复制
是'pending'，
```
CASE WHEN
```
登录后复制
返回
```
1
```
登录后复制
；否则，它隐式返回
```
NULL
```
登录后复制
。
```
COUNT(expression)
```
登录后复制
只计算非
```
NULL
```
登录后复制
值的数量，所以它能精确地统计出待处理订单的数量。
```
AVG(CASE WHEN status = 'completed' THEN amount END)
```
登录后复制
：与
```
COUNT
```
登录后复制
类似，
```
CASE WHEN
```
登录后复制
在条件不满足时返回
```
NULL
```
登录后复制
。
```
AVG
```
登录后复制
函数在计算平均值时会自动忽略
```
NULL
```
登录后复制
值，因此它只会计算已完成订单的平均金额。如果希望未完成订单计入分母但值为0，则需要写成
```
AVG(CASE WHEN status = 'completed' THEN amount ELSE 0 END)
```
登录后复制
，但这通常不是我们想要的平均值。

通过这种方式，我们可以在一个查询中，为每个客户生成一份包含多种条件聚合信息的报告，大大提升了查询效率和结果的可读性。

为什么常规的 WHERE 子句无法满足复杂条件下的聚合需求？

这确实是个常见的问题，很多人在刚接触SQL时都会尝试用

WHERE

登录后复制

来解决所有过滤问题。但

WHERE

登录后复制

子句的作用是在数据被聚合之前，过滤掉不符合条件的整行记录。它是一个“前置过滤器”。这意味着一旦一行数据被

WHERE

登录后复制

过滤掉了，它就永远不会参与到后续的聚合计算中。

阿里云-虚拟数字人

阿里云-虚拟数字人是什么？ ...

查看详情

想象一下，你想要统计一个部门里，男员工和女员工各自的平均薪资，并且希望这两个数字显示在同一行报表里。如果使用

WHERE

登录后复制

子句，你可能会写出两个独立的查询：一个

WHERE gender = 'Male'

登录后复制

，另一个

WHERE gender = 'Female'

登录后复制

。然后你需要将这两个结果合并，这无疑增加了复杂性。

而

CASE WHEN

登录后复制

嵌套在聚合函数中，则是在聚合函数内部进行条件判断。它允许你在聚合计算的“当下”，根据每行数据的具体情况，决定这行数据是否参与到某个特定的聚合计算中，或者以何种形式参与。它不是过滤掉整行，而是有选择性地处理行中的某个值。这样，所有原始数据行都参与了分组，但在聚合时，不同的条件分支可以针对同一行数据提取出不同的信息，并进行各自的聚合，最终在同一行结果中呈现出来，实现“横向”的条件聚合。这对于需要进行多维度对比分析的报表来说，简直是神来之笔。

在实际应用中，使用 CASE WHEN 和聚合函数有哪些常见的陷阱或性能考量？

在实际操作中，这种强大的组合也并非没有需要注意的地方。就像任何工具一样，用得好能事半功倍，用不好也可能带来一些麻烦。

NULL值处理的艺术： 这是最容易踩的坑。在
```
SUM
```
登录后复制
和
```
AVG
```
登录后复制
中，
```
ELSE 0
```
登录后复制
和
```
ELSE NULL
```
登录后复制
（或不写
```
ELSE
```
登录后复制
，默认就是
```
ELSE NULL
```
登录后复制
）的效果是天壤之别。
```
SUM
```
登录后复制
会忽略
```
NULL
```
登录后复制
，但会将
```
0
```
登录后复制
计入总和；
```
AVG
```
登录后复制
会忽略
```
NULL
```
登录后复制
，但会将
```
0
```
登录后复制
计入分母。所以，你必须清楚地知道自己是想让不符合条件的记录不参与计算（用
```
NULL
```
登录后复制
），还是以零值参与计算（用
```
0
```
登录后复制
）。比如，计算完成订单的平均金额，用
```
AVG(CASE WHEN status = 'completed' THEN amount END)
```
登录后复制
是正确的，因为不完成的订单不应该拉低平均值。但如果统计销售额，未完成的订单贡献为0，就应该用
```
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END)
```
登录后复制
。
性能考量： 尽管
```
CASE WHEN
```
登录后复制
在SQL中通常被高度优化，但在极端大数据量和极其复杂的
```
CASE WHEN
```
登录后复制
逻辑下，它确实会增加查询的CPU开销，因为数据库需要对每一行进行条件判断。如果你的
```
CASE WHEN
```
登录后复制
表达式非常复杂，或者包含大量的
```
OR
```
登录后复制
条件，并且这些条件涉及的列没有合适的索引，可能会导致全表扫描和额外的计算。在这种情况下，有时拆分成多个子查询或CTE（Common Table Expressions）可能会更清晰，甚至在某些数据库和特定场景下，性能反而更好。但大多数情况下，这种组合的性能是相当不错的，并且比多个独立查询再
```
UNION
```
登录后复制
或
```
JOIN
```
登录后复制
要高效得多。
代码可读性与维护： 当
```
CASE WHEN
```
登录后复制
内部的条件逻辑变得非常复杂，或者嵌套层级过深时，查询语句会变得难以阅读和理解。想象一下一个包含十几个
```
WHEN
```
登录后复制
子句，每个子句又包含复杂逻辑的
```
CASE WHEN
```
登录后复制
。这对于后期的维护者来说，无疑是个噩梦。在这种情况下，适当的注释、将复杂逻辑封装到视图或函数中，或者考虑重构业务逻辑，都是值得考虑的方案。保持代码的简洁和意图清晰，永远是第一位的。
数据类型匹配：
```
CASE WHEN
```
登录后复制
语句中，所有
```
THEN
```
登录后复制
和
```
ELSE
```
登录后复制
分支返回的值的数据类型应该兼容。如果不兼容，数据库可能会尝试进行隐式转换，这可能导致意想不到的结果，甚至报错。例如，一个分支返回字符串，另一个返回数字，可能会导致整个表达式被转换为字符串类型，从而影响后续的聚合计算。

除了 COUNT 和 SUM，还有哪些聚合函数可以与 CASE WHEN 有效结合？

CASE WHEN

登录后复制

与聚合函数的结合远不止

COUNT

登录后复制

和

SUM

登录后复制

。事实上，几乎所有的标准聚合函数都能以这种方式增强其功能，实现更精细化的数据分析。

AVG
登录后复制
(平均值): 刚才的例子中已经提到了。
```
AVG(CASE WHEN condition THEN value END)
```
登录后复制
可以计算满足特定条件的平均值，而忽略不满足条件的行。这在分析特定群体或特定事件的平均表现时非常有用，比如计算VIP客户的平均消费金额。
MAX
登录后复制
/
MIN
登录后复制
(最大值/最小值): 同样可以用于条件性的查找。例如，
```
MAX(CASE WHEN product_category = 'Electronics' THEN price END)
```
登录后复制
可以找出电子产品中的最高价格，而
```
MIN(CASE WHEN order_status = 'pending' THEN order_date END)
```
登录后复制
则能找出最早的待处理订单日期。这在需要从特定数据子集中提取极值时非常方便。
STRING_AGG
登录后复制
(SQL Server, PostgreSQL) 或
GROUP_CONCAT
登录后复制
(MySQL) (字符串拼接): 这个组合在需要根据条件拼接字符串时非常强大。比如，
```
STRING_AGG(CASE WHEN status = 'completed' THEN product_name END, ', ')
```
登录后复制
可以列出某个客户所有已完成订单中的产品名称，用逗号分隔。这比先过滤再拼接要简洁得多，尤其是在每个分组内有不同条件时。
VAR_POP
登录后复制
,
VAR_SAMP
登录后复制
,
STDEV_POP
登录后复制
,
STDEV_SAMP
登录后复制
(方差/标准差): 对于需要进行条件性统计学分析的场景，这些函数与
```
CASE WHEN
```
登录后复制
结合可以计算特定数据子集的方差或标准差。例如，分析不同产品线销售额的波动性，可以写成
```
STDEV_SAMP(CASE WHEN product_category = 'Clothing' THEN amount END)
```
登录后复制
。
APPROX_COUNT_DISTINCT
登录后复制
(近似去重计数): 在大数据场景下，如果需要对满足特定条件的唯一值进行近似计数，这个组合也能派上用场。例如，
```
APPROX_COUNT_DISTINCT(CASE WHEN country = 'USA' THEN customer_id END)
```
登录后复制
可以快速估算美国地区的独立客户数量。