mysql - 如何在不删除重复值的情况下对数据集进行分组

Question

attendence sql file in here我想在不删除重复值的情况下对数据集进行分组。

我尝试以下查询。我只能将一天与一次相关。

    SELECT 
        EnrolledID,
        Date,
        CASE WHEN Time > '1899-12-30 03:00:00' && Time <= '1899-12-30 12:15:00' THEN Time 
        END AS time_in ,
        CASE WHEN Time > '1899-12-30 12:15:00' && Time <= '1899-12-30 23:59:59' THEN Time 
        END AS time_out
    from attendance
        GROUP BY EnrolledID ;

上述查询的输出

EnrolledID      Date                time_in                 time_out    
23          2019-08-01 00:00:00     1899-12-30 07:54:40     NULL    
23          2019-08-01 00:00:00     NULL                    1899-12-30 16:01:40 
23          2019-08-02 00:00:00     1899-12-30 07:42:35      NULL   
23          2019-08-02 00:00:00    NULL                     1899-12-30 16:02:53 
23          2019-08-03 00:00:00     1899-12-30 07:37:41      NULL

预期输出。

EnrolledID      Date                time_in                 time_out    
    23          2019-08-01 00:00:00     1899-12-30 07:54:40     1899-12-30 16:01:40 
    23          2019-08-01 00:00:00     1899-12-30 07:42:35                 1899-12-30 16:01:40 
    23          2019-08-02 00:00:00     1899-12-30 07:42:35     1899-12-31 16:15:33 
    23          2019-08-02 00:00:00     1899-12-30 07:11:51                     1899-12-30 16:02:53 
    23          2019-08-03 00:00:00     1899-12-30 07:37:41      1899-12-31 16:15:00

Answer 1

你可以试试这个。由于您已经在查询中设置了time in 和time out，因此您可以使用union all 轻松获得所需的结果。

WITH CTE AS (
SELECT COUNT(Date) 
    EnrolledID,
    Date,
    CASE WHEN Time > '1899-12-30 03:00:00' && Time <= '1899-12-30 12:15:00' THEN Time 
    END AS time_in ,
    CASE WHEN Time > '1899-12-30 12:15:00' && Time <= '1899-12-30 23:59:59' THEN Time 
    END AS time_out
from attendance
    GROUP BY EnrolledID 
)
, CT AS (
    SELECT  EnrolledID, DATE, TIME_IN AS TIME FROM CTE WHERE TIME_IN IS NOT NULL 
    UNION ALL
    SELECT  EnrolledID, DATE, TIME_OUT FROM CTE WHERE TIME_OUT IS NOT NULL
)
SELECT EnrolledID, DATE , TIME FROM CTE ORDER BY DATE, TIME

如果他们每个日期都有多个记录，而您只需要特定的记录，那么您可以在 ct 部分应用相同的过滤器以获得所需的输出。

尽管如果您共享您的表架构和示例输出，那么最好提供任何建议。