时间戳

首页 > 解决方案 > 如何从不同的表中获取记录并忽略 SQL 中的重复日期？

sql - 如何从不同的表中获取记录并忽略 SQL 中的重复日期？

问题描述

自从我使用SQL以来已经很长时间了，所以我一直在努力解决以下问题。我有 2 个类似于这些的表：

Table: Users                       Table: Checker             
             
UID| name | department            UID|        date_time        | entry |  real_entry  
---  -----  ----------            ---  ------------------------  ------  -------------
1  | Anne |  TI                   1  | 2021-02-22 00:00:00.000 |  0500 |    0500 
2  | Joe  |  Sales                2  | 2021-02-22 00:00:00.000 |  1500 |    1500 
3  | Bob  |  TI                   2  | 2021-02-23 00:00:00.000 |  0600 |    0600 
4  | Ava  |  Sales                3  | 2021-02-22 00:00:00.000 |  0500 |    0510 
                                  3  | 2021-02-22 00:00:00.000 |  1900 |    1900 
                                  3  | 2021-02-23 00:00:00.000 |  0500 |    0500 
                                  4  | 2021-02-22 00:00:00.000 |  1900 |    1910 
                                  4  | 2021-02-19 00:00:00.000 |  0600 |    0610

表格检查器用作公司人员的数字输入时钟系统。所以'entry'是他们应该检查的时间，'real_entry'是他们实际检查的时间。

我需要获得的是每个用户本周的出勤率。如果他们每天检查两次，我只需要检索第一个。结果表应如下所示：

Result:

UID| name | department |        date_time        | entry |  real_entry   
---  -----  ----------   ------------------------  ------  -------------
1  | Anne |  TI        | 2021-02-22 00:00:00.000 |  0500 |    0500 
2  | Joe  |  Sales     | 2021-02-22 00:00:00.000 |  1500 |    1500 
3  | Bob  |  TI        | 2021-02-22 00:00:00.000 |  0500 |    0510 
4  | Ava  |  Sales     | 2021-02-22 00:00:00.000 |  1900 |    1910  
2  | Joe  |  Sales     | 2021-02-23 00:00:00.000 |  0600 |    0600 
3  | Bob  |  TI        | 2021-02-23 00:00:00.000 |  0500 |    0500

想象一下今天是 2021/02/24，这意味着他们四个人在 22 日参加，但在 23 日只有 Bob 和 Joe 去。我没有计算 22 的重复条目，也没有计算 19 的条目。

对于此示例，周开始应为：'2021-02-22 00:00:00.000'，周末应为：'2021-02-28 00:00:00.000'

到目前为止，我有这个：

DECLARE @WEEKSTART NVARCHAR(100),
        @WEEKEND   NVARCHAR(100)

SET @WEEKSTART = (SELECT Dateadd(day, 2 - Datepart(weekday, Getdate()), Cast(
                                                  Floor(Cast(Getdate() AS
                                                             FLOAT
                         )) AS DATETIME)))
SET @WEEKEND = (SELECT Dateadd(day, 8 - Datepart(weekday, Getdate()), Cast(
                                              Floor(Cast(Getdate() AS
                                                         FLOAT
                       )) AS DATETIME)))

SELECT checker.uid,
       users.NAME,
       users.department,
       checker.date_time,
       checker.entry,
       checker.entry,
FROM   checker
       INNER JOIN users
               ON users.uid = checker.uid
WHERE  checker.date_time BETWEEN @WEEKSTART AND @WEEKEND
ORDER  BY USER.NAME,
          checker.date_time

但是这样，我仍然获得了 Checker 表的所有记录。

关于如何达到预期结果的任何建议？

我尝试按 Checker.date_time 和 Checker.UID 分组，但我得到：在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。我得到了，但我想不出任何其他解决方案。

我希望我能很好地解释自己，并提前感谢您的帮助:)

标签： sqlsql-server

解决方案

这可以通过使用带有行号的额外临时表来查找第一个条目来实现。


        DECLARE @WEEKSTART NVARCHAR(100),
                @WEEKEND NVARCHAR(100)
    
        SET @WEEKSTART = (SELECT DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), cast( floor( cast( getdate() as float)) as datetime)))
        SET @WEEKEND = (SELECT  DATEADD(DAY, 8 -  DATEPART(WEEKDAY, GETDATE()) , cast( floor( cast( getdate() as float)) as datetime)))

        DROP TABLE IF EXISTS #TempResult
         SELECT CH.UID,
               U.name,
               U.department,
               CH.date_time,
               CH.entry,
               CH.Real_Entry,
               ROW_NUMBER() OVER (PARTITION BY U.name,CH.date_time order by CH.entry) AS EntryOrder
               INTO #TempResult
          FROM Checker CH         
          JOIN Users U ON U.UID = CH.UID          
          WHERE CH.date_time BETWEEN @WEEKSTART AND @WEEKEND    
          ORDER BY U.name, CH.date_time
    
         SELECT UID,
                name,
                department,
                date_time,
                entry,
                Real_Entry 
        FROM #TempResult 
        WHERE EntryOrder = 1

推荐阅读