VB.NET实现员工月度考勤报表展示及SQL查询问题求助

Hey there! Let's break down your problem and fix it step by step.

First, why is your current query only returning 1 result? That's likely because only one employee has check-in records in April 2018 in your ATTENDANCE table. If you just want to see all employees for April (including those with zero check-ins), we'll cover that too—but let's start with your core goal: building an automatic monthly attendance report for all employees across all months.

Solution 1: Report attendance days per employee per month (only months with check-ins) #

This query groups data by employee and month, so you'll get a row for every employee-month combination where there's at least one check-in. The syntax varies slightly by database:

MySQL/MariaDB #

SELECT 
    EMPLOYEEID,
    DATE_FORMAT(checkinDate, '%Y-%m') AS report_month,
    COUNT(DISTINCT checkinDate) AS attendance_days
FROM ATTENDANCE
GROUP BY EMPLOYEEID, DATE_FORMAT(checkinDate, '%Y-%m')
ORDER BY EMPLOYEEID, report_month;

PostgreSQL #

SELECT 
    EMPLOYEEID,
    DATE_TRUNC('month', checkinDate)::DATE AS month_start,
    COUNT(DISTINCT checkinDate) AS attendance_days
FROM ATTENDANCE
GROUP BY EMPLOYEEID, DATE_TRUNC('month', checkinDate)
ORDER BY EMPLOYEEID, month_start;

SQL Server #

SELECT 
    EMPLOYEEID,
    DATEFROMPARTS(YEAR(checkinDate), MONTH(checkinDate), 1) AS month_start,
    COUNT(DISTINCT checkinDate) AS attendance_days
FROM ATTENDANCE
GROUP BY EMPLOYEEID, YEAR(checkinDate), MONTH(checkinDate)
ORDER BY EMPLOYEEID, month_start;

Solution 2: Full monthly report (include employees with zero check-ins) #

If you need a complete report that shows every employee for every month (even if they never checked in that month), you'll need to generate a list of all months and all employees first, then join with the attendance data. Here's how to do it in MySQL:

-- Generate all months we want to report on (adjust start date as needed)
WITH all_months AS (
    SELECT DATE_FORMAT('2018-01-01' + INTERVAL (a.a + 10*b.a + 100*c.a) MONTH, '%Y-%m') AS report_month
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2) c
    WHERE '2018-01-01' + INTERVAL (a.a + 10*b.a + 100*c.a) MONTH <= CURDATE()
),
-- Get all unique employees (replace EMPLOYEE with your actual employee table name)
all_employees AS (
    SELECT DISTINCT EMPLOYEEID FROM EMPLOYEE
)
-- Combine everything and calculate attendance days
SELECT 
    ae.EMPLOYEEID,
    am.report_month,
    COALESCE(COUNT(DISTINCT a.checkinDate), 0) AS attendance_days
FROM all_employees ae
CROSS JOIN all_months am
LEFT JOIN ATTENDANCE a 
    ON ae.EMPLOYEEID = a.EMPLOYEEID 
    AND DATE_FORMAT(a.checkinDate, '%Y-%m') = am.report_month
GROUP BY ae.EMPLOYEEID, am.report_month
ORDER BY ae.EMPLOYEEID, am.report_month;

Fixing your original April-only query (show all employees) #

If you just want to fix your original query to show all employees for April 2018 (including those with no check-ins), use a left join with your employee table:

SELECT 
    e.EMPLOYEEID,
    COUNT(DISTINCT a.checkinDate) AS April_attendance_days
FROM EMPLOYEE e
LEFT JOIN ATTENDANCE a 
    ON e.EMPLOYEEID = a.EMPLOYEEID 
    AND a.checkinDate BETWEEN '2018-04-01' AND '2018-04-30'
GROUP BY e.EMPLOYEEID
ORDER BY e.EMPLOYEEID;

This will return a row for every employee, with 0 if they had no check-ins in April.

内容的提问来源于stack exchange，提问作者Aaron Lee Herng Yue