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

=================需要导出 合并2,加没有企业,和增加了时间,上级部门============================
select 
a.dept_name2 '上级部门',
a.dept_name '部门名称',
a.count '用户数量',
b.loginCount '登录次数'
-- a.*,b.* 
from 
(
SELECT 
count(1) count,
sys_dept.dept_name ,
sys_dept2.dept_name as dept_name2
 FROM frame_user
LEFT JOIN sys_dept on sys_dept.id=frame_user.department
LEFT JOIN sys_dept as sys_dept2 on sys_dept2.id=sys_dept.parent_id
where  sys_dept.parent_id !='1222530386878267392'
GROUP BY frame_user.department

) as a 

left join 

(

SELECT
	sum( frame_user.logincount ) AS loginCount,
	sys_dept.dept_name 
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 and frame_op_log.oper_at>='2025-01-05' and frame_op_log.oper_at<'2025-01-05'
	GROUP BY
		frame_op_log.oper_name 
	) AS frame_user
	LEFT JOIN sys_dept ON sys_dept.id = frame_user.department 
GROUP BY
	frame_user.department
	
	
	) as b on b.dept_name=a.dept_name



====================================================================

SELECT * from frame_role_data where id='1287820787952123904'
桥梁
SELECT * from frame_user WHERE id in(SELECT user_id from frame_role_data_user where role_data_id=(SELECT id from frame_role_data where id='1287820787952123904')) 

 内涝
SELECT username from frame_user WHERE id in(SELECT user_id from frame_role_data_user where role_data_id=(SELECT id from frame_role_data where id='1287820476273393664'))


燃气
SELECT username from frame_user WHERE id in(SELECT user_id from frame_role_data_user where role_data_id=(SELECT id from frame_role_data where id='1287820383654772736'))


=================================================================================


select 
a.dept_name2 '上级部门',
a.dept_name '部门名称',
a.username '用户账号',
a.truename '用户名称'
-- a.*,b.* 
from 
(
SELECT 
frame_user.truename,
frame_user.username,
sys_dept.dept_name ,
sys_dept2.dept_name as dept_name2
 FROM frame_user
LEFT JOIN sys_dept on sys_dept.id=frame_user.department
LEFT JOIN sys_dept as sys_dept2 on sys_dept2.id=sys_dept.parent_id
where  sys_dept.parent_id !='1222530386878267392'


) as a 
	ORDER BY a.dept_name2,a.dept_name