如何统计各员工status=9且日期重复的行数及总行数？

Got it, let's tackle that tricky count_same_date_status_9 column you're stuck on. The key is to avoid hardcoding dates and use window functions to dynamically calculate how many times each (employee_id, date, status) combination appears. Here's a working SQL query that matches your expected results:

SELECT
    employee_id,
    SUM(CASE 
        WHEN status = 9 AND date_status_count >= 2 THEN 1 
        ELSE 0 
    END) AS count_same_date_status_9,
    COUNT(*) AS count_all
FROM (
    SELECT
        employee_id,
        status,
        date,
        -- Calculate how many times this (employee, date, status) trio appears
        COUNT(*) OVER (PARTITION BY employee_id, date, status) AS date_status_count
    FROM your_table_name -- Replace with your actual table name
) AS subquery
GROUP BY employee_id
ORDER BY employee_id;

How This Works: #

• Subquery with Window Function: The inner subquery uses COUNT(*) OVER (PARTITION BY employee_id, date, status) to count how many rows exist for each unique combination of employee, date, and status. For example, for employee 1's status=9 rows on 10/19/2020, this will return 2 for both rows.
• Calculate count_same_date_status_9: The outer query uses a SUM(CASE...) to tally up only the rows where:
  • The status is 9, AND
  • The count of that (employee, date, status) combination is at least 2 (meaning the date repeats for status=9 under this employee)
• Total Rows per Employee: COUNT(*) simply gives the total number of rows for each employee, which is your count_all column.

Let's Verify Against Your Expected Results: #

• Employee 1: 2 rows meet the status=9 + repeated date condition, total rows 3 → matches your expected 2 | 3
• Employee 2: Both status=9 rows have unique dates (so their date_status_count is 1), so sum is 0, total rows 3 → 0 | 3
• Employee 3: No status=9 rows, sum is 0, total rows 2 → 0 | 2
• Employee 4: All 3 status=9 rows share the same date, so sum is 3, total rows 3 → 3 | 3

This approach is flexible and won't break if new dates are added to your table later.

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