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 

请您参考如下方法:

你可以试试这个。由于您已经在查询中设置了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 部分应用相同的过滤器以获得所需的输出。

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