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

===============================这是查询不同部门的账号人数======================================
SELECT 
count(1),
sys_dept.dept_name
 FROM frame_user
LEFT JOIN sys_dept on sys_dept.id=frame_user.department
GROUP BY frame_user.department

// 不要企业数据
SELECT 
count(1),
sys_dept.dept_name
 FROM frame_user
LEFT JOIN sys_dept on sys_dept.id=frame_user.department
where  sys_dept.parent_id !='1222530386878267392'
GROUP BY frame_user.department
===============================这是查询不同部门的登录次数======================================

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 
	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

//加时间段

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>='2024-12-02' and frame_op_log.oper_at<'2024-12-09'
	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
=================需要导出 合并2,加没有企业,和增加了时间============================
select 
a.dept_name '部门名称',
a.count '用户数量',
b.loginCount '登录次数'
-- a.*,b.* 
from 
(
SELECT 
count(1) count,
sys_dept.dept_name 
 FROM frame_user
LEFT JOIN sys_dept on sys_dept.id=frame_user.department
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>='2024-12-09' and frame_op_log.oper_at<'2024-12-15'
	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 
(
SELECT 
count(1) accountCount,
sys_dept.dept_name
 FROM frame_user
LEFT JOIN sys_dept on sys_dept.id=frame_user.department

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 
	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