关联attendance/employees/ctc_master三表查询指定员工数据需求

我拥有attendance、employees、ctc_master三张数据库表，表结构及数据如下。此前已实现单表查询指定时间段内出勤status=1的员工计数，现需关联三张表，查询active=1的员工的employee_id、出勤日期对应的年份、status=1的出勤次数、对应年份的ctc。已编写关联查询语句但未得到预期结果，预期输出字段为：employee_id、出勤日期年份、status=1的计数、ctc。

表结构 #

attendance表 #

CREATE TABLE `attendance` (
    `attendance_id` bigint(100) NOT NULL AUTO_INCREMENT,
    `attendence_date` varchar(50) DEFAULT NULL,
    `status` int(11) DEFAULT NULL,
    `employee_id` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`attendance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

employees表 #

CREATE TABLE `employees` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `active` tinyint(1) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb4 COLLATE=latin1;

ctc_master表 #

CREATE TABLE `ctc_master` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `employee_id` bigint(20) DEFAULT NULL,
    `year` varchar(250) DEFAULT NULL,
    `ctc` decimal(14,2) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

表数据 #

attendance表 #

employees表 #

ctc_master表 #

已尝试的查询语句 #

单表查询语句 #

select count(*) , employee_id from attendance atd where status = 1 and 
attendence_date between '2022-10-01'  and '2022-10-30' group by employee_id ; 

关联查询语句 #

select ctc.employee_id, ctc.ctc , ctc.year from  employees emp  
join ctc_master_tbl ctc on  emp.id = ctc.employee_id  
join attendance atd on emp.id = atd.employee_id 
and emp.id  = atd.employee_id and  emp.id = ctc.employee_id  where emp.active =1 and  
atd.attendence_date between '2022-01-28'  and '2022-01-31' ;

问题分析与正确查询语句 #

之前的关联查询存在几个问题：

1. 表名错误：ctc_master_tbl应为ctc_master
2. 未对出勤记录按员工和年份分组统计status=1的次数
3. 日期范围设置错误（示例数据中出勤日期均为2023年，原语句查询2022年）
4. 未过滤status=1的出勤记录
5. 未关联出勤年份与CTC表的年份

正确的查询语句如下：

SELECT
    emp.id AS employee_id,
    YEAR(atd.attendence_date) AS attend_year,
    COUNT(atd.attendance_id) AS present_count,
    ctc.ctc
FROM employees emp
JOIN attendance atd ON emp.id = atd.employee_id
JOIN ctc_master ctc ON emp.id = ctc.employee_id AND YEAR(atd.attendence_date) = ctc.year
WHERE emp.active = 1
    AND atd.status = 1
    -- 可根据需求调整日期范围
    AND atd.attendence_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY emp.id, YEAR(atd.attendence_date), ctc.ctc;

结果说明 #

执行上述语句后，会得到符合要求的结果：

若需包含无出勤记录的活跃员工（显示0次出勤），可将JOIN attendance改为LEFT JOIN attendance，此时无记录时present_count会显示0。

内容的提问来源于stack exchange，提问作者user2531602