查询_指定年月有业务事实的用户_BASE_USER.sql
4.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- =============================================================================
-- 与后端 LqMonthlyEmployeeFactHelper / LqMonthlyEmployeeFactQueryService 口径一致：
-- 统计某自然月内在考勤、业绩、消耗、金三角、各岗位工资归档等任一来源出现过的员工（BASE_USER.F_Id）。
-- 用途：DBA 核对、报表、或在不调用 API 时直接查库。
-- 使用：修改下方 @stat_year、@stat_month 后整段执行（MySQL 5.7+ / 8.0+）。
-- 说明：jkszh/kjblszh 等与 F_ACCOUNT 或 F_Id 匹配；仅排除 BASE_USER 软删（F_DeleteMark IS NULL）。
-- 不新建表、不改表结构。
-- =============================================================================
SET @stat_year := 2026;
SET @stat_month := 3;
SET @d0 := STR_TO_DATE(CONCAT(@stat_year, '-', LPAD(@stat_month, 2, '0'), '-01'), '%Y-%m-%d');
SET @d1 := LAST_DAY(@d0);
SET @d1_open := DATE_ADD(@d1, INTERVAL 1 DAY);
SET @month_str := DATE_FORMAT(@d0, '%Y%m');
SELECT DISTINCT u.F_Id AS user_id,
u.F_REALNAME AS real_name,
u.F_ACCOUNT AS account,
u.F_GW AS gw,
u.F_ENABLEDMARK AS enabled_mark,
u.F_IsOnJob AS is_on_job
FROM (
SELECT F_UserId AS k FROM lq_attendance_summary
WHERE F_Year = @stat_year AND F_Month = @stat_month AND F_IsEffective = 1
UNION
SELECT F_EmployeeId FROM lq_employee_store_assignment
WHERE F_IsEffective = 1 AND F_StartDate <= @d1 AND (F_EndDate IS NULL OR F_EndDate >= @d0)
UNION
SELECT F_UserId FROM lq_attendance_record
WHERE F_AttendanceDate >= @d0 AND F_AttendanceDate <= @d1 AND F_IsEffective = 1
UNION
SELECT F_PersonId FROM lq_person_times_record
WHERE F_WorkMonth = @month_str AND F_IsEffective = 1
UNION
SELECT user_id FROM lq_jinsanjiao_user
WHERE F_Month = @month_str AND F_DeleteMark = 0
UNION
SELECT jkszh FROM lq_kd_jksyj
WHERE yjsj >= @d0 AND yjsj < @d1_open AND F_IsEffective = 1 AND jkszh IS NOT NULL AND jkszh <> ''
UNION
SELECT jkszh FROM lq_xh_jksyj
WHERE yjsj >= @d0 AND yjsj < @d1_open AND F_IsEffective = 1 AND jkszh IS NOT NULL AND jkszh <> ''
UNION
SELECT jkszh FROM lq_hytk_jksyj
WHERE tksj >= @d0 AND tksj < @d1_open AND F_IsEffective = 1 AND jkszh IS NOT NULL AND jkszh <> ''
UNION
SELECT kjblszh FROM lq_kd_kjbsyj
WHERE yjsj >= @d0 AND yjsj < @d1_open AND F_IsEffective = 1 AND kjblszh IS NOT NULL AND kjblszh <> ''
UNION
SELECT k.kjblszh
FROM lq_xh_kjbsyj k
INNER JOIN lq_xh_hyhk h ON k.glkdbh = h.F_Id AND h.F_IsEffective = 1
WHERE k.F_IsEffective = 1 AND h.Hksj >= @d0 AND h.Hksj < @d1_open
AND k.kjblszh IS NOT NULL AND k.kjblszh <> ''
UNION
SELECT kjblszh FROM lq_hytk_kjbsyj
WHERE tksj >= @d0 AND tksj < @d1_open AND F_IsEffective = 1 AND kjblszh IS NOT NULL AND kjblszh <> ''
UNION
SELECT F_EmployeeId FROM lq_salary_statistics WHERE F_StatisticsMonth = @month_str
UNION
SELECT F_EmployeeId FROM lq_tech_teacher_salary_statistics WHERE F_StatisticsMonth = @month_str
UNION
SELECT F_EmployeeId FROM lq_tech_general_manager_salary_statistics WHERE F_StatisticsMonth = @month_str
UNION
SELECT F_EmployeeId FROM lq_director_salary_statistics WHERE F_StatisticsMonth = @month_str
UNION
SELECT F_EmployeeId FROM lq_store_manager_salary_statistics WHERE F_StatisticsMonth = @month_str
UNION
SELECT F_EmployeeId FROM lq_assistant_salary_statistics WHERE F_StatisticsMonth = @month_str
UNION
SELECT F_EmployeeId FROM lq_major_project_director_salary_statistics WHERE F_StatisticsMonth = @month_str
UNION
SELECT F_EmployeeId FROM lq_major_project_teacher_salary_statistics WHERE F_StatisticsMonth = @month_str
UNION
SELECT F_EmployeeId FROM lq_business_unit_manager_salary_statistics WHERE F_StatisticsMonth = @month_str
) raw
INNER JOIN BASE_USER u ON (u.F_Id = raw.k OR u.F_ACCOUNT = raw.k)
WHERE u.F_DeleteMark IS NULL
ORDER BY u.F_REALNAME;