postgresql---一个字段不同条件下的统计结果

需求：计算d_group表中name字段在status=‘0111’,status=‘0112’,status='0113’三种状态下的值

1.创建数据表如下所示

2.添加数据后如下所示

3.查询语句如下所示

WITH T AS (
	SELECT T
		."name",
		T.status,
		COUNT ( * ) AS C 
	FROM
		d_group T 
	WHERE
		T.status IN ( '0111', '0112', '0113' ) 
	GROUP BY
		T."name",
		T.status 
	ORDER BY
		T.status 
	) SELECT T
	.NAME,
	SUM ( CASE WHEN status = '0111' THEN C ELSE 0 END ) "status_0111",
	SUM ( CASE WHEN status = '0112' THEN C ELSE 0 END ) "status_0112",
	SUM ( CASE WHEN status = '0113' THEN C ELSE 0 END ) "status_0113" 
FROM
T 
WHERE
	T.NAME IS NOT NULL 
	AND T.NAME != '' 
GROUP BY
	T.NAME

4.查询结果如下所示