SQL Server 条件比较

Question

我有两个表：

CREATE TABLE #HOURS
(DAY INTEGER,

HOUR INTEGER）

CREATE TABLE #PERSONS
(DAY INTEGER, HOUR INTEGER,
 Name NVARCHAR(50))
GO

INSERT #HOURS VALUES (1, 5)
INSERT #HOURS VALUES (1, 6)
INSERT #HOURS VALUES (1, 8)
INSERT #HOURS VALUES (1, 10)
INSERT #HOURS VALUES (1, 14)
INSERT #HOURS VALUES (1, 15)
INSERT #HOURS VALUES (1, 16)
INSERT #HOURS VALUES (1, 17)
INSERT #HOURS VALUES (1, 18)


INSERT #PERSONS VALUES (1, 5, 'Steve')
INSERT #PERSONS VALUES (1, 6, 'Steve')
INSERT #PERSONS VALUES (1, 7, 'Steve')
INSERT #PERSONS VALUES (1, 8, 'Steve')
INSERT #PERSONS VALUES (1, 10, 'Steve')
INSERT #PERSONS VALUES (1, 14, 'Steve')
INSERT #PERSONS VALUES (1, 15, 'Steve')
INSERT #PERSONS VALUES (1, 16, 'Steve')
INSERT #PERSONS VALUES (1, 17, 'Steve')

INSERT #PERSONS VALUES (1, 10, 'Jim')
INSERT #PERSONS VALUES (1, 11, 'Jim')
INSERT #PERSONS VALUES (1, 12, 'Jim')
INSERT #PERSONS VALUES (1, 13, 'Jim')

GO

Hours 显示工作时间，#Persons 显示按小时进入系统的人员。 我想找到工作时间与工作时间表相符的人。但他或她可以跳过两个工作时间。

我已经尝试过：

select t.Day, sum(t.Nulls)
from  
(select h.Hour, h.Day
      , Case   
            WHEN p.Hour is null Then 1 ELSE 0 END Nulls
from #HOURS h  
left join #PERSONS P on h.Hour = p.Hour AND h.Day = p.Day) t  
group by t.Day
HAVING sum(t.Nulls) < 2

但这仅在同一天没有不同的人时才有效；）

有什么建议吗？

Answer 1

我认为您的意思是，对于日期和人员的每个组合，如果该人当天工作的有效小时数在有效小时数的 2 小时之内，您希望返回该值那天存在。如果是这样，这应该可以解决问题：

SELECT
    Day
    , Name
    , HoursWorked
    , HoursInDay
FROM (
    SELECT
        p.Day
        , p.Name
        , COUNT(*) HoursWorked
        , (SELECT COUNT(*) FROM #Hours H2 WHERE H2.Day = P.Day) HoursInDay
    FROM
        #Persons P INNER JOIN #Hours H 
            ON P.Day = H.Day And P.Hour = H.Hour
    GROUP BY
        p.Day, p.Name
     ) Data
WHERE
    HoursWorked + 2 >= HoursInDay

Answer 2

我认为您仍然需要进一步澄清您期望的结果。与此同时，这会让你开始吗？

SELECT    p.DAY, p.Name, WORKED = COUNT(*), WORKHOURS = AVG(hc.WORKHOURS)
FROM      #PERSONS p
          INNER JOIN #HOURS h ON h.DAY = p.DAY AND h.HOUR = p.HOUR
          LEFT OUTER JOIN ( 
            SELECT    DAY, WORKHOURS = COUNT(*)
            FROM      #HOURS
            GROUP BY  DAY
          ) hc ON hc.DAY = p.DAY
GROUP BY  p.DAY, p.Name