数据库 sql

查询比例 近一年的数据 分组查询

SELECT
	t1.pc_handling_guideline_suggestion_satisfaction satisfaction,
	ROUND(
		COUNT(
			t1.pc_handling_guideline_suggestion_id
		) * 100 / (
			SELECT
				COUNT(1)
			FROM
				t_portal_pc_handling_guideline_suggestion a,
				t_portal_pc_handling_guideline b
			WHERE
				a.pc_handling_guideline_suggestion_guideline_code = b.portal_pc_handling_guideline_code
			AND a.pc_handling_guideline_suggestion_state = 1
			AND DATE_FORMAT(
				a.pc_handling_guideline_suggestion_time,
				'%Y-%m-%d'
			) >= DATE_FORMAT(
				DATE_ADD(NOW(), INTERVAL - 1 YEAR),
				'%Y-%m-%d'
			)
		),
		2
	)
VALUE
FROM
	t_portal_pc_handling_guideline_suggestion t1,
	t_portal_pc_handling_guideline t2
WHERE
	t1.pc_handling_guideline_suggestion_guideline_code = t2.portal_pc_handling_guideline_code
AND t1.pc_handling_guideline_suggestion_state = 1
AND DATE_FORMAT(
	t1.pc_handling_guideline_suggestion_time,
	'%Y-%m-%d'
) >= DATE_FORMAT(
	DATE_ADD(NOW(), INTERVAL - 1 YEAR),
	'%Y-%m-%d'
)
AND t1.pc_handling_guideline_suggestion_satisfaction != '1'
GROUP BY
	t1.pc_handling_guideline_suggestion_satisfaction

-- 5非常满意 4满意 3基本满意 2不满意 1非常不满

查询没有主体的事项

-- 统计没有主题的事项
select 
	g.portal_pc_handling_guideline_code as '事项code',
	g.portal_pc_handling_guideline_name as '事项名称',
	gs.pc_guideline_scene_name as '主题名称'
from t_portal_pc_handling_guideline g 
left join t_portal_pc_guideline_scene_guideline gsg on gsg.pc_guideline_scene_guideline_guideline_id = g.portal_pc_handling_guideline_id
left join t_portal_pc_guideline_scene gs on gsg.pc_guideline_scene_guideline_scene_code = gs.pc_guideline_scene_code
where g.portal_pc_handling_guideline_state != 99 and (gs.pc_guideline_scene_name is null or gs.pc_guideline_scene_name = '')
group by g.portal_pc_handling_guideline_id