mysql - iReport中的员工考勤日历报表设计

Question

关闭。这个问题需要debugging details .它目前不接受答案。












想改善这个问题吗？更新问题，使其成为 on-topic对于堆栈溢出。

去年关闭。




Improve this question




我有不同的表格来跟踪员工出勤和假期，现在所有事情都已完成并且工作正常我想为面临一些问题的员工月度日历报告创建报告。

首先是

所有表都是:

1. hr_employee

2. hr_biometric出勤

1. hr_employee_leave

1. hr_employee_visits

1. hr_holidays

1. 15天前所有周六为工作日，15天后所有周六为公司假期

2. 在假期记录的日期范围内从日期到日期不是所有其他表的单个日期假期

我创建了一个查询，但它检索了多条记录，这对我来说是一个很难编写的查询。我怎样才能做到这一点？

这是查询:

    SET @fromDate = "2019-12-01";
SET @toDate = "2019-12-31";
SET @empId = 2814;
SET @recordStatus = "ATT";
SELECT 
att.time_checked AS checkedTIme, @recordStatus
FROM
hr_employee emp 
JOIN hr_biometric_attendance att ON att.`employee_id` = emp.id
WHERE emp.id = @empId
AND att.`time_checked` BETWEEN @fromDate AND @toDate

UNION
SELECT v.selected_date, "HD"  FROM 
(SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
,
(SELECT hDay.date_from df, hDay.date_to dt FROM `hr_holiday` hDay WHERE hDay.date_from >= @fromDate AND hDay.date_to <= @toDate) hDayQ
WHERE selected_date BETWEEN hDayQ.df AND hDayQ.dt

UNION
SELECT v1.selected_date1, "EV" FROM 
(SELECT ADDDATE('1970-01-01',t41.i*10000 + t31.i*1000 + t21.i*100 + t11.i*10 + t01.i) selected_date1 FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t01,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t11,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t21,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t31,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t41) v1
,
(SELECT hEmpV.date_from df, hEmpV.date_to dt FROM `hr_employee_visit` hEmpV WHERE hEmpV.employee_id = @empId) hEmpVQ
WHERE selected_date1 BETWEEN hEmpVQ.df AND hEmpVQ.dt


UNION
SELECT v2.selected_date2, "EL" FROM 
(SELECT ADDDATE('1970-01-01',t42.i*10000 + t32.i*1000 + t22.i*100 + t12.i*10 + t02.i) selected_date2 FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t02,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t12,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t22,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t32,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t42) v2
,
(SELECT hEmpL.date_from df, hEmpL.date_to dt FROM `hr_employee_leave` hEmpL WHERE hEmpL.employee_id = @empId) hEmpLQ
WHERE selected_date2 BETWEEN hEmpLQ.df AND hEmpLQ.dt

UNION DISTINCT
SELECT v3.selected_date3, (SELECT IF(DAYNAME(v3.selected_date3) ='Sunday' OR (DAYOFMONTH(v3.selected_date3) >= 15 AND DAYNAME(v3.selected_date3) ='saturday'),'H','W'))
FROM 
(SELECT ADDDATE('1970-01-01',t43.i*10000 + t33.i*1000 + t23.i*100 + t13.i*10 + t03.i) selected_date3 FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t03,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t13,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t23,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t33,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t43) v3
WHERE selected_date3 BETWEEN @fromDate AND @toDate

ORDER BY `checkedTIme`;

这是erd表:



这是预期结果报告的图像:



这是上述查询的 Jasper 结果:

Answer 1

就像我在评论中所说的那样，为了简化查询，您应该创建一个专用 calendar表，以便您可以缩短查询。引用:https://www.db-fiddle.com/f/qVgmfDaVUszABAyFzH7iUS/0

完成后，您可以按如下方式重新编写查询:

SET @fromDate = "2019-12-01";
SET @toDate = "2019-12-31";
SET @empId = 2814;
SET @recordStatus = "ATT";

SELECT att.time_checked AS checkedTIme, @recordStatus FROM hr_employee emp 
JOIN hr_biometric_attendance att ON att.`employee_id` = emp.id
WHERE emp.id = @empId
AND att.`time_checked` BETWEEN @fromDate AND @toDate

UNION
SELECT v.selected_dates, "HD"  FROM calendar v JOIN
(SELECT hDay.date_from df, hDay.date_to dt FROM `hr_holiday` hDay 
WHERE hDay.date_from >= @fromDate AND hDay.date_to <= @toDate) hDayQ
ON selected_dates BETWEEN hDayQ.df AND hDayQ.dt

UNION
SELECT v1.selected_dates, "EV" FROM calendar v1 JOIN
(SELECT hEmpV.date_from df, hEmpV.date_to dt FROM `hr_employee_visit` hEmpV 
WHERE hEmpV.employee_id = @empId) hEmpVQ
ON selected_dates BETWEEN hEmpVQ.df AND hEmpVQ.dt

UNION
SELECT v2.selected_dates, "EL" FROM calendar v2 JOIN
(SELECT hEmpL.date_from df, hEmpL.date_to dt FROM `hr_employee_leave` hEmpL 
WHERE hEmpL.employee_id = @empId) hEmpLQ
ON selected_dates BETWEEN hEmpLQ.df AND hEmpLQ.dt

UNION DISTINCT
SELECT v3.selected_dates, (SELECT IF(DAYNAME(v3.selected_dates) ='Sunday' 
OR (DAYOFMONTH(v3.selected_dates) >= 15 AND DAYNAME(v3.selected_dates) ='saturday'),'H','W')) 
FROM calendar v3
WHERE selected_dates BETWEEN @fromDate AND @toDate

ORDER BY `checkedTIme`;

现在它更容易阅读，然后您可以开始构建您的最终查询。