PostgreSQL状态统计、人员多个角色

状态统计

<!-- 获取状态分类统计(COALESCE:返回第一个不是NULL的参数)-->
<select id="getStateStatistics" resultType="java.util.Map">
	SELECT
		COUNT ( * ) AS all,
		COALESCE ( SUM ( CASE WHEN STATE = 'processing' THEN 1 ELSE 0 END ), 0 ) AS processing,
		COALESCE ( SUM ( CASE WHEN STATE = 'notreceive' THEN 1 ELSE 0 END ), 0 ) AS notreceive,
		COALESCE ( SUM ( CASE WHEN STATE = 'completed' THEN 1 ELSE 0 END ), 0 ) AS completed
	FROM
		ro_repair_order
</select>

人员多个角色

SELECT
	su.*,
	(SELECT sd.dept_name FROM sys_depts sd WHERE sd.dept_id = su.dept_id)AS dept_name,
	( SELECT
		array_to_string(
			ARRAY (
				SELECT
					sr.role_name
				FROM
					sys_roles sr
				WHERE
				sr.role_id IN ( SELECT sur.role_id FROM sys_user_role sur WHERE sur.user_id = su.id )
			), ','
		)
	) AS role_name
FROM
	sys_users su