text

1	lingxi_statistics_login 省公司及18地市登录人次统计信息表

1

2

查询每个部门下，不通系统来源对应的人数和人次

text

1	SELECT IF(tmp.dept_name IS NOT NULL, tmp.dept_name, tmp2.dept_name) AS dept_name,
2	sl.sys_code,
3	COUNT(1) AS login_count,
4	COUNT(DISTINCT sl.user_id) AS user_count
5	FROM lingxi_statistics_login sl
6	LEFT JOIN lingxi_sys_dept sd ON sl.dept_id = sd.dept_id
7	LEFT JOIN (SELECT dept_id,
8	dept_name,
9	IF(ancestors = '1901845967065358337,1901845968856326146',
10	ancestors,
11	CONCAT(ancestors, ',', dept_id)) AS ancestors
12	FROM lingxi_sys_dept
13	WHERE (ancestors = '1901845967065358337'
14	OR ancestors = '1901845967065358337,1901845968856326146')
15	AND dept_id NOT IN ('1901845968856326146', '1901845968856326147',
16	'1901845968919240710', '1901845968919240712')
17	AND del_flag = 0) tmp ON sl.dept_id = tmp.dept_id
18	LEFT JOIN (SELECT dept_id,
19	dept_name,
20	CONCAT(ancestors, ',', dept_id) AS ancestors
21	FROM lingxi_sys_dept
22	WHERE (ancestors = '1901845967065358337'
23	OR ancestors = '1901845967065358337,1901845968856326146')
24	AND dept_id NOT IN ('1901845968856326146', '1901845968856326147',
25	'1901845968919240710', '1901845968919240712')
26	AND del_flag = 0) tmp2
27	ON sd.ancestors LIKE CONCAT(tmp2.ancestors, '%') and tmp.dept_name is null
28	WHERE sl.sys_code IS NOT NULL
29	and sl.login_time between #{startTime} and #{endTime}
30	GROUP BY dept_name, sl.sys_code;

用来设置每个部门下，pc和h5总登录人数

text

1	SELECT IF(tmp.dept_name IS NOT NULL, tmp.dept_name, tmp2.dept_name) AS dept_name,
2	CASE
3	WHEN sl.sys_code IN (1, 2, 5) THEN 'pc'
4	WHEN sl.sys_code IN (3, 4) THEN 'h5'
5	ELSE 'other'
6	END AS sys_group,
7	COUNT(DISTINCT sl.user_id) AS user_count
8	FROM lingxi_statistics_login sl
9	LEFT JOIN lingxi_sys_dept sd on sl.dept_id = sd.dept_id
10	LEFT JOIN (SELECT dept_id,
11	dept_name,
12	IF(ancestors = '1901845967065358337,1901845968856326146',
13	ancestors,
14	CONCAT(ancestors, ',', dept_id)) AS ancestors
15	FROM lingxi_sys_dept
16	WHERE (ancestors = '1901845967065358337'
17	OR ancestors = '1901845967065358337,1901845968856326146')
18	AND dept_id NOT IN ('1901845968856326146', '1901845968856326147',
19	'1901845968919240710', '1901845968919240712')
20	AND del_flag = 0) tmp ON sl.dept_id = tmp.dept_id
21	LEFT JOIN (SELECT dept_id,
22	dept_name,
23	CONCAT(ancestors, ',', dept_id) AS ancestors
24	FROM lingxi_sys_dept
25	WHERE (ancestors = '1901845967065358337'
26	OR ancestors = '1901845967065358337,1901845968856326146')
27	AND dept_id NOT IN ('1901845968856326146', '1901845968856326147',
28	'1901845968919240710', '1901845968919240712')
29	AND del_flag = 0) tmp2
30	ON sd.ancestors LIKE CONCAT(tmp2.ancestors, '%') and tmp.dept_name is null
31	where sl.login_time between #{startTime} and #{endTime}
32	GROUP BY dept_name, sys_group;