sql case when

最新推荐文章于 2024-06-29 15:12:22 发布

原创最新推荐文章于 2024-06-29 15:12:22 发布 · 675 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文展示如何使用SQL CASE WHEN语句按小区统计错误类型总数、新错误数量、已完成错误数量和已废弃错误数量。通过聚合查询，实现数据的精细化分析。

使用sql case when 统计
SELECT XiaoQu ,count(1) errorTotal,sum(CASE WHEN ErrorState='new' THEN 1 ELSE 0 end) AS newError,
sum(CASE WHEN ErrorState='finish' THEN 1 ELSE 0 end) AS finish,sum(CASE WHEN ErrorState='discard' THEN 1 ELSE 0 end) AS discard
FROM [sam].[sam_errorList]

GROUP BY XiaoQu;

SELECT t2.error_type, sum(CASE WHEN t1.ErrorState='new' THEN 1 ELSE 0 end) AS newError,
sum(CASE WHEN t1.ErrorState='finish' THEN 1 ELSE 0 end) AS finish,sum(CASE WHEN t1.ErrorState='discard' THEN 1 ELSE 0 end) AS discard
FROM [sam].[sam_errorList] t1,sam.sam_errorType t2
WHERE t1.ErrorID = t2.hid and XiaoQu = '鑫馨家园'
GROUP BY t2.error_type;