programs_info/无锡市生命线项目/朱千里/统计查询语句.txt

===============================这是查询每个专项每个区的账号人数======================================
SELECT 
frame_role_data.role_name,
count(1),
count(sys_dept.dept_name like '%市政%' or null) as '市政',
count(sys_dept.dept_name like '%江阴%' or null) as '江阴',
count(sys_dept.dept_name like '%宜兴%' or null) as '宜兴',
count(sys_dept.dept_name like '%梁溪区%' or null) as '梁溪区',
count(sys_dept.dept_name like '%锡山区%' or null) as '锡山区',
count(sys_dept.dept_name like '%惠山区%' or null) as '惠山区',
count(sys_dept.dept_name like '%滨湖区%' or null) as '滨湖区',
count(sys_dept.dept_name like '%新吴区%' or null) as '新吴区',
count(sys_dept.dept_name like '%经开区%'or null) as '经开区'
 FROM frame_user
LEFT JOIN sys_dept on sys_dept.id=frame_user.department
left join frame_role_data_user on frame_user.id=frame_role_data_user.user_id
left join frame_role_data on frame_role_data.id=frame_role_data_user.role_data_id
GROUP BY frame_role_data.role_name,



===============================这是查询每个专项每个区的登录次数======================================
select 
sum(frame_user.logincount),
frame_role_data.role_name,
sum( if (sys_dept.dept_name like '%市政%' ,frame_user.logincount,null)) as '市政',
sum( if (sys_dept.dept_name like '%江阴%' ,frame_user.logincount,null)) as '江阴',
sum( if (sys_dept.dept_name like '%宜兴%' ,frame_user.logincount,null)) as '宜兴',
sum( if (sys_dept.dept_name like '%梁溪区%' ,frame_user.logincount,null)) as '梁溪区',
sum( if (sys_dept.dept_name like '%锡山区%' ,frame_user.logincount,null)) as '锡山区',
sum( if (sys_dept.dept_name like '%惠山区%' ,frame_user.logincount,null)) as '惠山区',
sum( if (sys_dept.dept_name like '%滨湖区%' ,frame_user.logincount,null)) as '滨湖区',
sum( if (sys_dept.dept_name like '%新吴区%' ,frame_user.logincount,null)) as '新吴区',
sum( if (sys_dept.dept_name like '%经开区%' ,frame_user.logincount,null)) as '经开区'
 from 
(SELECT
	frame_op_log.oper_name,
	frame_user.id,
	frame_user.department,
	count( 1 ) AS logincount 
FROM
	frame_op_log
	LEFT JOIN frame_user ON frame_user.username = frame_op_log.oper_name 
WHERE
	frame_op_log.error_msg = '登录正常' 
	AND frame_user.id IS NOT NULL 
GROUP BY
	frame_op_log.oper_name) as frame_user
	LEFT JOIN sys_dept on sys_dept.id=frame_user.department
left join frame_role_data_user on frame_user.id=frame_role_data_user.user_id
left join frame_role_data on frame_role_data.id=frame_role_data_user.role_data_id
GROUP BY frame_role_data.role_name

===========================下面是校验数据用的=============================================
SELECT 
*

 FROM frame_user
LEFT JOIN sys_dept on sys_dept.id=frame_user.department
left join frame_role_data_user on frame_user.id=frame_role_data_user.user_id
left join frame_role_data on frame_role_data.id=frame_role_data_user.role_data_id
where sys_dept.dept_name like '%江阴%' and
frame_role_data.role_name ='地下管线'



马宏波


SELECT
	frame_op_log.*
FROM
	frame_op_log
	LEFT JOIN frame_user ON frame_user.username = frame_op_log.oper_name 
WHERE
	frame_op_log.error_msg = '登录正常' 
	AND frame_user.id IS NOT NULL 
and 
	frame_op_log.oper_name='马宏波'