纯查询语句，不用function，不用存储过程，不用with，不用declare,实现sqlserver计算两个日期的小时差，排除周六周日

<think>我们有一个挑战：在SQLServer中计算两个日期之间的工作日小时差（排除周六和周日），而且不能使用函数、存储过程、CTE（WITHAS）或变量声明（DECLARE）。我们只能使用查询语句。思路：1.我们需要一个方法来表示两个日期之间的每一天，并且判断每一天是否是工作日（周一至周五）。2.由于不能使用CTE或变量，我们可能无法使用递归CTE来展开日期。我们可以考虑使用数字辅助表（TallyTable）来生成日期序列，但也不能使用CTE或变量，所以我们需要一种方法来生成数字序列。3.我们可以使用系统表（如master..spt_values）来生成数字序列，或者使用其他可用的系统表。但是，为了不依赖特定系统表（因为不同环境可能不同），我们可以考虑使用一组已知的行来生成数字。例如，我们可以使用多个表的CROSSJOIN来生成足够多的行，但这样可能效率不高，而且我们需要确保生成的数字序列足够覆盖日期差。4.另一种方法是使用SELECTTOP(DATEDIFF(DAY,@StartDate,@EndDate))...但是我们不能使用变量，所以我们需要将开始日期和结束日期直接写在查询中，或者作为参数？但题目要求使用查询语句，我们可以假设开始日期和结束日期是已知的，直接替换。步骤：假设我们有两个日期：'2023-01-01'和'2023-01-10'（作为示例）。计算总小时差，排除周六、周日：工作日小时数：假设一天工作24小时？还是指定工作时间？题目没有指定，我们假设是24小时制，即每个工作日算24小时。所以，我们需要计算两个日期之间有多少个工作日（周一至周五），然后乘以24。但是，如果两个日期不是整天，比如精确到小时，那么我们需要计算：例如：开始时间为'2023-01-0114:00:00'，结束时间为'2023-01-0310:00:00'那么我们需要：第1天：2023-01-01（周日）->排除第2天：2023-01-02（周一）->全天24小时第3天：2023-01-03（周二）->只计算到10:00，即10小时因此，我们需要考虑部分天。由于不能使用函数和存储过程，我们需要一个方法来拆分开始日期和结束日期之间的每一天，并计算每一天的工作小时数。但是，由于不能使用CTE和递归，我们很难拆分每一天。因此，我们可能需要分情况处理：1.如果开始日期和结束日期是同一天，那么只需要判断这一天是否是工作日，如果是，则计算两个时间点之间的小时差；否则为0。2.如果跨天，我们可以将时间分为三部分：a.开始日期当天（从开始时间到当天结束）b.结束日期当天（从当天开始到结束时间）c.中间的完整天（整天）但是，如何生成中间的那些日期呢？没有CTE，没有递归，我们可能只能使用一个数字表（通过系统表或大量连接生成）来得到这些日期。考虑到这些限制，我们尝试以下方法：方法一：使用数字辅助表（通过系统表生成）我们可以使用系统表master..spt_values（如果可用）来获取一组数字。例如：SELECTnumberFROMmaster..spt_valuesWHEREtype='P'ANDnumberBETWEEN0ANDDATEDIFF(DAY,StartDate,EndDate)但是，我们无法使用变量，所以我们需要将日期直接写入查询。方法二：如果我们不能依赖系统表，我们可以创建一个足够大的数字序列。例如，使用多个表的CROSSJOIN生成一个行数很大的表，然后用ROW_NUMBER()来生成序列。但注意，我们只能使用查询语句，不能创建临时表或表变量。由于题目要求不使用function、存储过程、CTE或declare，我们可以使用内联方式生成数字序列。这里我们采用方法二，生成一个0到999的数字序列（假设日期差不超过1000天）。我们可以使用多个表的CROSSJOIN，例如：SELECTTOP1000ROW_NUMBER()OVER(ORDERBY(SELECTNULL))-1ASnFROMsys.objectsa,sys.objectsb,sys.objectsc但是，我们也可以使用其他表（如系统视图）来生成。步骤：1.计算两个日期之间的天数差：DATEDIFF(DAY,StartDate,EndDate)2.生成0到天数差的数字序列（代表第0天，第1天，...，第n天）3.对于每一天，计算日期：StartDate+n天4.判断该日期是星期几：使用DATEPART(dw,date)注意：SETDATEFIRST设置会影响一周的第一天。我们假设星期天是7，星期六是6？实际上，DATEPART(dw)返回的值取决于SETDATEFIRST。我们可以用@@DATEFIRST吗？但为了确保，我们可以用表达式：当星期天或星期六时排除。通常，星期天是1，星期六是7（如果SETDATEFIRST为7，则可能不同）。为了保险，我们可以这样判断：如果星期几的值是1（星期天）或7（星期六）则排除。5.然后，我们计算每一天应该计算的小时数：-对于第一天（n=0）：计算从开始时间到当天结束的时间（即24:00:00）的小时数-对于最后一天（n=天数差）：计算从当天开始（00:00:00）到结束时间的小时数-对于中间的天（n从1到天数差-1）：如果是工作日，则计算24小时，否则0。6.但是，我们如何将数字序列与我们的日期计算结合起来？具体步骤：假设我们有两个日期：@StartDate和@EndDate，但在查询中我们不能使用变量，所以我们在查询中直接替换为字面值。例如：开始日期为'2023-01-0114:00:00'，结束日期为'2023-01-0310:00:00'第一步：计算总天数差DECLARE@DaysDiffINT=DATEDIFF(DAY,'2023-01-0114:00:00','2023-01-0310:00:00')--2天但是我们不能用变量，所以我们直接写进表达式。第二步：生成数字序列（0,1,2）第三步：对于每个数字n，计算对应的日期：DATEADD(DAY,n,CAST(CAST('2023-01-0114:00:00'ASDATE)ASDATETIME))--这样得到的是那一天的00:00:00第四步：计算每个部分：n=0（第一天）：日期：2023-01-0100:00:00->但是我们要从14:00开始，所以小时数=24-DATEPART(HOUR,'2023-01-0114:00:00')...不对，这样计算不准确，因为还有分钟。实际上，我们应该计算开始时间到当天结束的时间（即24:00:00，也就是第二天的00:00:00）的时间差，然后转换为小时（小数）。小时数=DATEDIFF(SECOND,'2023-01-0114:00:00','2023-01-0200:00:00')/3600.0但是注意，如果开始日期是2023-01-0114:00:00，那么到当天的结束（2023-01-0200:00:00）是10小时（因为24-14=10，但实际计算应该用时间差）n=1（第二天）：日期：2023-01-0200:00:00（一整天）->24小时n=2（最后一天）：日期：2023-01-0300:00:00->从00:00:00到10:00:00->10小时但是，注意：我们生成的数字序列只到2（因为天数差是2），所以最后一天就是n=2（即结束日期的那天）。但是，我们如何判断每一天是周几？对于n=0，我们判断日期'2023-01-01'（即开始日期的日期部分）是周几？如果是周六或周日，则第一天的小时数不计。第五步：将每一天计算的小时数相加。问题：如何生成数字序列？由于不能使用CTE和变量，我们使用内联生成。以下是一个生成0到999的数字序列的查询：SELECTTOP(DATEDIFF(DAY,'2023-01-0114:00:00','2023-01-0310:00:00')+1)ROW_NUMBER()OVER(ORDERBY(SELECTNULL))-1ASnFROMsys.objectsa,sys.objectsb注意：sys.objects是系统视图，通常有足够的行（比如几百行），两个表交叉连接应该足够1000天。然后，我们将这个数字序列用于计算每一天。但是，我们需要在同一个查询中计算小时数，并且汇总。我们可以使用SUM和CASE表达式。具体查询结构：SELECTSUM(CASE...END)ASTotalHoursFROM(--生成数字序列SELECTTOP(DATEDIFF(DAY,StartDate,EndDate)+1)ROW_NUMBER()OVER(ORDERBY(SELECTNULL))-1ASnFROMsys.objectsa,sys.objectsb)ASNumbersCROSSAPPLY(--计算当前数字对应的日期（日期部分）SELECTCurrentDate=DATEADD(DAY,n,CAST(StartDateASDATE)))ASDCROSSAPPLY(--计算当前日期是星期几SELECTWeekday=DATEPART(dw,CurrentDate))ASWCROSSAPPLY(--计算这一天应该计算的小时数SELECTHours=CASEWHENn=0THEN--第一天CASEWHENWeekdayNOTIN(1,7)THEN--不是周末DATEDIFF(SECOND,StartDate,DATEADD(DAY,1,CAST(StartDateASDATE)))/3600.0ELSE0ENDWHENn=DATEDIFF(DAY,StartDate,EndDate)THEN--最后一天CASEWHENWeekdayNOTIN(1,7)THENDATEDIFF(SECOND,CAST(EndDateASDATE),EndDate)/3600.0ELSE0ENDELSE--中间天CASEWHENWeekdayNOTIN(1,7)THEN24ELSE0ENDEND)ASH但是，这里有几个问题：1.我们多次使用了StartDate和EndDate，而且不能使用变量。所以我们需要在查询中多次写相同的日期字面值。2.对于最后一天的计算：我们计算的是从当天开始（00:00:00）到结束时间的小时数。但是，我们使用CAST(EndDateASDATE)得到的是结束日期的日期部分（即当天的00:00:00），然后计算这个时间到EndDate的秒数，再除以3600得到小时数（小数）。3.对于第一天，我们计算的是从StartDate到第二天00:00:00（即DATEADD(DAY,1,CAST(StartDateASDATE))）的秒数，然后除以3600。4.关于星期几的判断：这里我们假设星期日是1，星期六是7。但是，这取决于@@DATEFIRST的设置。为了确保，我们可以设置SETDATEFIRST7，但查询中不能使用SET。所以我们可以用另一种方式：判断星期几的名称？不行，因为不能使用FORMAT（SQLServer2012以上有，但这里不依赖）。我们可以用以下方式避免：使用表达式：(DATEPART(dw,CurrentDate)+@@DATEFIRST-1)%7+1？这样很复杂，而且我们不知道@@DATEFIRST。或者，我们直接使用已知的：星期日是1，星期六是7，并且假设系统设置是默认的（星期日为第一天）。如果系统设置不同，可能会出错。另一种方法是：不管@@DATEFIRST，我们判断星期几用名称？不行，因为不能使用转换函数到名称。我们可以用：如果星期几是1（星期日）或者7（星期六）就是周末，但这是默认设置（DATEFIRST=7）的情况。在DATEFIRST=1的情况下，星期日是1，星期六是6？不对，DATEFIRST设置不会改变星期六和星期日的值，它只改变一周的第一天。但是DATEPART(dw)返回的值会随着DATEFIRST变化吗？实际上，DATEPART(dw)返回的值依赖于语言设置和DATEFIRST设置。为了保险，我们可以这样：判断星期几是否为星期六或星期日，我们可以用：IFDATENAME(dw,CurrentDate)IN('Saturday','Sunday')但是，DATENAME返回的是字符串，并且依赖于语言设置。如果服务器是英语，那么是'Saturday','Sunday'，如果是中文，则返回'星期六','星期日'。所以也不行。因此，我们只能依赖数字，并且要求设置一致。我们可以先设置语言为英文，但查询中不能设置。所以这是一个风险点。或者，我们可以用另一种方法：计算当前日期与一个已知的星期日的日期差，然后取模7，如果余0则是星期日，余6则是星期六？这样就不受设置影响。例如：已知一个星期日：'1900-01-07'（星期日）？注意1900-01-01是星期一，所以1900-01-07是星期日。那么：DATEDIFF(DAY,'1900-01-07',CurrentDate)%7如果余0，则是星期日；余6，则是星期六。所以：判断周末：如果(DATEDIFF(DAY,'1900-01-07',CurrentDate)%7)IN(0,6)那么就是周末。但是，注意：模运算在SQLServer中可以用%运算符。因此，我们可以这样写：CASEWHEN(DATEDIFF(DAY,'1900-01-07',CurrentDate)%7)IN(0,6)THEN'Weekend'ELSE'Weekday'END但是，注意：1900-01-07是星期日，那么：1900-01-07->0%7=0->星期日1900-01-08->1%7=1->星期一...1900-01-13->6%7=6->星期六所以，余0是星期日，余6是星期六。这样判断是准确的，而且不依赖于@@DATEFIRST和语言设置。因此，我们替换星期几的判断为这个计算。5.另外，我们需要注意，开始日期和结束日期可能是同一天，那么n=0也是最后一天，所以我们需要在同一天时只计算一次。但是我们的数字序列在DATEDIFF(DAY)为0时，TOP(1)生成一个数字0，然后我们计算第一天和最后一天？实际上，在同一天时，我们只需要计算从开始时间到结束时间，并且判断这一天是否是工作日。因此，我们需要修改CASEWHEN：如果DATEDIFF(DAY,StartDate,EndDate)=0，那么这一天既是第一天也是最后一天，我们只计算从开始时间到结束时间的小时数，并且判断是否是工作日。所以，我们可以这样：CASEWHENn=0ANDn=DATEDIFF(DAY,StartDate,EndDate)THEN--同一天CASEWHEN(DATEDIFF(DAY,'1900-01-07',CurrentDate)%7)NOTIN(0,6)THENDATEDIFF(SECOND,StartDate,EndDate)/3600.0ELSE0ENDWHENn=0THEN--第一天（但不是最后一天）CASEWHEN(DATEDIFF(DAY,'1900-01-07',CurrentDate)%7)NOTIN(0,6)THENDATEDIFF(SECOND,StartDate,DATEADD(DAY,1,CAST(StartDateASDATE)))/3600.0ELSE0ENDWHENn=DATEDIFF(DAY,StartDate,EndDate)THEN--最后一天（但不是第一天）CASEWHEN(DATEDIFF(DAY,'1900-01-07',CurrentDate)%7)NOTIN(0,6)THENDATEDIFF(SECOND,CAST(EndDateASDATE),EndDate)/3600.0ELSE0ENDELSECASEWHEN(DATEDIFF(DAY,'1900-01-07',CurrentDate)%7)NOTIN(0,6)THEN24ELSE0ENDEND但是，注意：在同一天时，我们生成的数字序列只有一个数字0，所以会进入第一个条件。最后，我们写出完整的查询（假设开始日期和结束日期分别用字面值'2023-01-0114:00:00'和'2023-01-0310:00:00'）：注意：我们需要将开始日期和结束日期替换为实际的日期值。查询如下：DECLARE@StartDateDATETIME='2023-01-0114:00:00';--示例开始日期DECLARE@EndDateDATETIME='2023-01-0310:00:00';--示例结束日期但是我们不能用变量，所以直接替换：SELECTSUM(CASEWHENn=0ANDn=DATEDIFF(DAY,'2023-01-0114:00:00','2023-01-0310:00:00')THEN--同一天CASEWHEN(DATEDIFF(DAY,'1900-01-07',DATEADD(DAY,n,CAST('2023-01-0114:00:00'ASDATE)))%7)NOTIN(0,6)THENDATEDIFF(SECOND,'2023-01-0114:00:00','2023-01-0310:00:00')/3600.0ELSE0ENDWHENn=0THENCASEWHEN(DATEDIFF(DAY,'1900-01-07',DATEADD(DAY,n,CAST('2023-01-0114:00:00'ASDATE)))%7)NOTIN(0,6)THENDATEDIFF(SECOND,'2023-01-0114:00:00',DATEADD(DAY,1,CAST('2023-01-0114:00:00'ASDATE)))/3600.0ELSE0ENDWHENn=DATEDIFF(DAY,'2023-01-0114:00:00','2023-01-0310:00:00')THENCASEWHEN(DATEDIFF(DAY,'1900-01-07',DATEADD(DAY,n,CAST('2023-01-0114:00:00'ASDATE)))%7)NOTIN(0,6)THENDATEDIFF(SECOND,CAST('2023-01-0310:00:00'ASDATE),'2023-01-0310:00:00')/3600.0ELSE0ENDELSECASEWHEN(DATEDIFF(DAY,'1900-01-07',DATEADD(DAY,n,CAST('2023-01-0114:00:00'ASDATE)))%7)NOTIN(0,6)THEN24ELSE0ENDEND)ASTotalHoursFROM(SELECTTOP(DATEDIFF(DAY,'2023-01-0114:00:00','2023-01-0310:00:00')+1)ROW_NUMBER()OVER(ORDERBY(SELECTNULL))-1ASnFROMsys.objectsa,sys.objectsb)ASNumbers但是，这个查询中我们重复写了很多次日期字面值，而且计算星期几的部分重复了多次。我们可以使用CROSSAPPLY来避免重复，但这里为了清晰，我们先这样写。注意：在计算最后一天的小时数时，我们使用了CAST(EndDateASDATE)来得到结束日期的日期部分（即当天的00:00:00），然后计算它到结束时间的时间差（秒），再除以3600得到小时数。但是，如果结束日期的时间部分正好是00:00:00，那么最后一天的时间差为0，这是合理的。另外，我们生成数字序列的子查询中，使用sys.objects的两个交叉连接，应该能生成足够的行（如果sys.objects有100行，那么100*100=10000行，足够1000天）。但为了确保，我们可以使用三个表交叉连接。测试：将开始日期和结束日期设置为同一天，并且是周末，应该返回0；如果是工作日，则返回两个时间点的小时差。但是，这个查询很长，而且重复了日期字面值多次。我们可以用以下技巧：将开始日期和结束日期放在子查询中，然后CROSSJOIN到数字序列中。这样，我们可以写一次日期字面值。改进：SELECTTotalHours=SUM(WorkHours)FROM(SELECTn=ROW_NUMBER()OVER(ORDERBY(SELECTNULL))-1FROMsys.objectsa,sys.objectsb)ASNumbersCROSSJOIN(SELECTStartDate='2023-01-0114:00:00',EndDate='2023-01-0310:00:00')ASDWHERENumbers.n<=DATEDIFF(DAY,StartDate,EndDate)--使用TOP和WHERE都可以，这里用WHERE来过滤CROSSAPPLY(SELECTDaysDiff=DATEDIFF(DAY,StartDate,EndDate))ASDDCROSSAPPLY(SELECTCurrentDate=DATEADD(DAY,n,CAST(StartDateASDATE)))ASCDCROSSAPPLY(SELECTDayOfWeek=(DATEDIFF(DAY,'1900-01-07',CD.CurrentDate)%7))ASDWCROSSAPPLY(SELECTWorkHours=CASEWHENn=0ANDn=DD.DaysDiffTHEN--同一天CASEWHENDW.DayOfWeekNOTIN(0,6)THENDATEDIFF(SECOND,StartDate,EndDate)/3600.0ELSE0ENDWHENn=0THEN--第一天CASEWHENDW.DayOfWeekNOTIN(0,6)THENDATEDIFF(SECOND,StartDate,DATEADD(DAY,1,CAST(StartDateASDATE)))/3600.0ELSE0ENDWHENn=DD.DaysDiffTHEN--最后一天CASEWHENDW.DayOfWeekNOTIN(0,6)THENDATEDIFF(SECOND,CAST(EndDateASDATE),EndDate)/3600.0ELSE0ENDELSE--中间天CASEWHENDW.DayOfWeekNOTIN(0,6)THEN24ELSE0ENDEND)ASWH这样，我们只需要在CROSSJOIN的子查询中写一次日期字面值。注意：在计算DayOfWeek时，我们使用了%7，并且得到0（星期日）和6（星期六）为周末。但是，在计算最后一天的小时数时，我们使用了CAST(EndDateASDATE)来得到结束日期的日期部分（即当天的00:00:00），然后计算它到结束时间的时间差（秒）。注意，如果结束时间正好是00:00:00，那么时间差为0，这是合理的。但是，如果结束时间是在开始时间之前（即结束时间小于开始时间）？题目没有说明，我们假设结束时间>=开始时间。现在，我们将其写成一个完整的查询，只需要替换开始日期和结束日期即可。注意：我们使用CROSSJOIN来引入开始日期和结束日期，然后使用多个CROSSAPPLY来逐步计算。由于不能使用变量，我们只能这样写。但是，这个查询在SQLServer中运行，并且要求有sys.objects视图的访问权限。如果sys.objects不可用，我们可以使用其他表（如任意用户表）来生成数字序列。如果没有任何表可用，我们可以使用VALUES子句构造一个小的序列，然后使用多个CROSSJOIN来扩展，但这样很麻烦。因此，我们假设存在sys.objects。最终，我们写出的查询如下（替换日期字面值为实际值）：SELECTTotalHours=SUM(WorkHours)FROM(SELECTn=ROW_NUMBER()OVER(ORDERBY(SELECTNULL))-1FROMsys.objectsa,sys.objectsb)ASNumbersCROSSJOIN(SELECTStartDate='2023-01-0114:00:00',--替换为实际开始日期EndDate='2023-01-0310:00:00'--替换为实际结束日期)ASDCROSSAPPLY(SELECTDaysDiff=DATEDIFF(DAY,D.StartDate,D.EndDate))ASDDWHERENumbers.n<=DD.DaysDiff--生成0到DaysDiff的数字CROSSAPPLY(SELECTCurrentDate=DATEADD(DAY,Numbers.n,CAST(D.StartDateASDATE)))ASCDCROSSAPPLY(--计算当前日期相对于1900-01-07（星期日）的天数差，然后模7，余0为星期日，余6为星期六SELECTDayOfWeek=(DATEDIFF(DAY,'1900-01-07',CD.CurrentDate)%7))ASDWCROSSAPPLY(SELECTWorkHours=CASEWHENNumbers.n=0ANDNumbers.n=DD.DaysDiffTHEN--同一天CASEWHENDW.DayOfWeekNOTIN(0,6)THENDATEDIFF(SECOND,D.StartDate,D.EndDate)/3600.0ELSE0ENDWHENNumbers.n=0THEN--第一天CASEWHENDW.DayOfWeekNOTIN(0,6)THENDATEDIFF(SECOND,D.StartDate,DATEADD(DAY,1,CAST(D.StartDateASDATE)))/3600.0ELSE0ENDWHENNumbers.n=DD.DaysDiffTHEN--最后一天CASEWHENDW.DayOfWeekNOTIN(0,6)THENDATEDIFF(SECOND,CAST(D.EndDateASDATE),D.EndDate)/3600.0ELSE0ENDELSE--中间天CASEWHENDW.DayOfWeekNOTIN(0,6)THEN24ELSE0ENDEND)ASWH注意：我们使用了多个CROSSAPPLY来简化表达式。现在，我们可以测试这个查询。但是，这个查询非常复杂，而且效率可能不高（因为生成了大量的行）。如果日期差很大（比如几年），那么生成的行数就很多。因此，在实际应用中，如果日期差很大，可能需要考虑其他方法。但题目要求不使用函数、存储过程、CTE等，所以只能这样。另外，我们假设了1900-01-07是星期日，这个假设是成立的（因为1900-01-01是星期一，那么1900-01-07就是星期日）。最后，我们返回TotalHours。如果用户需要精确到分钟，我们可以修改为分钟，然后除以60.0，但题目要求小时差，我们使用秒除以3600.0，可以保留小数部分。注意：我们使用了/3600.0，这样可以得到小数小时。例如：30分钟就是0.5小时。因此，这个查询可以计算小数小时。但是，由于题目要求不使用function、存储过程、CTE、declare，我们使用了内联生成数字序列和复杂的CASE表达式，符合要求。我们可以将上述查询作为最终答案。注意：如果开始日期或结束日期为NULL，或者结束日期小于开始日期，那么这个查询可能不会返回正确结果。我们假设输入是有效的，且结束日期>=开始日期。如果结束日期小于开始日期，我们可以用类似的方法，但题目没有要求，我们假设结束日期>=开始日期。因此，我们提供以上查询。</think>在SQL Server中，仅使用查询语句计算两个日期之间的工作日小时差（排除周六、周日），且不借助函数、存储过程、CTE或变量声明，可通过以下步骤实现： ### 解决方案 ```sql SELECT SUM(WorkHours) AS TotalWorkHours FROM ( SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM sys.objects a, sys.objects b -- 生成数字序列 ) AS Numbers CROSS JOIN ( SELECT StartDate = '2023-01-01 14:00:00', -- 替换为实际开始时间 EndDate = '2023-01-10 10:30:00' -- 替换为实际结束时间 ) AS Dates CROSS APPLY ( SELECT DaysDiff = DATEDIFF(DAY, Dates.StartDate, Dates.EndDate) ) AS Calc CROSS APPLY ( SELECT CurrentDate = DATEADD(DAY, Numbers.n, CAST(Dates.StartDate AS DATE)) ) AS CD CROSS APPLY ( SELECT DayOfWeek = (DATEDIFF(DAY, '1900-01-07', CD.CurrentDate) % 7) -- 1900-01-07为星期日 ) AS DW CROSS APPLY ( SELECT WorkHours = CASE -- 同一天处理 WHEN Numbers.n = 0 AND Numbers.n = Calc.DaysDiff THEN CASE WHEN DW.DayOfWeek NOT IN (0, 6) -- 0=周日,6=周六 THEN DATEDIFF(SECOND, Dates.StartDate, Dates.EndDate) / 3600.0 ELSE 0 END -- 第一天部分 WHEN Numbers.n = 0 THEN CASE WHEN DW.DayOfWeek NOT IN (0, 6) THEN DATEDIFF(SECOND, Dates.StartDate, DATEADD(DAY, 1, CAST(Dates.StartDate AS DATE))) / 3600.0 ELSE 0 END -- 最后一天部分 WHEN Numbers.n = Calc.DaysDiff THEN CASE WHEN DW.DayOfWeek NOT IN (0, 6) THEN DATEDIFF(SECOND, CAST(Dates.EndDate AS DATE), Dates.EndDate) / 3600.0 ELSE 0 END -- 整天部分 ELSE CASE WHEN DW.DayOfWeek NOT IN (0, 6) THEN 24 ELSE 0 END END ) AS WH WHERE Numbers.n <= Calc.DaysDiff -- 限定日期范围 ``` ### 关键说明 1. **数字序列生成** 通过`sys.objects`系统表交叉连接生成序列（`n=0,1,2,...`），覆盖日期差范围内的所有天数。 2. **工作日判定** - 使用基准日期`1900-01-07`（星期日）计算模7余数： - **余0**：星期日 - **余6**：星期六 - 公式：`(DATEDIFF(DAY, '1900-01-07', CurrentDate) % 7)` 3. **分段小时计算** | 时间段 | 计算方式 | |-------------|-------------------------------------------------------------------------| | 同一天 | 直接计算开始到结束的秒数 → 转换为小时 | | 首日 | 计算开始时间到次日00:00的秒数 → 转换为小时 | | 末日 | 计算当日00:00到结束时间的秒数 → 转换为小时 | | 完整工作日 | 直接计24小时 | 4. **精确度** - 使用`DATEDIFF(SECOND, ...) / 3600.0`保留小数小时（如1.5小时）。 - 秒级精度确保跨天计算准确。 5. **使用注意** - 替换`StartDate`和`EndDate`为实际值。 - 确保结束日期 **≥** 开始日期。 - `sys.objects`需有足够行覆盖最大日期差（不足时可增加`CROSS JOIN sys.objects c`）。 ### 示例计算 - **输入**： `StartDate = '2023-01-02 14:00:00'`（周一）, `EndDate = '2023-01-03 10:30:00'`（周二） - **输出**： `24小时（周一） + 10.5小时（周二） = 34.5小时` > 此方案通过纯查询实现，严格规避了函数、CTE等限制，核心依赖日期数学和集合运算[^3]。 --- ### 相关问题 1. **如何修改查询以排除节假日？** > 需额外关联节假日表，例如： > ```sql > LEFT JOIN HolidayTable H ON CD.CurrentDate = H.HolidayDate > WHERE H.HolidayDate IS NULL -- 过滤节假日 > ``` 2. **若需要精确到分钟如何调整？** > 替换`DATEDIFF(SECOND)`为`DATEDIFF(MINUTE)`并除以`60.0`，例如： > ```sql > DATEDIFF(MINUTE, ...) / 60.0 > ``` 3. **SQL Server中是否有内置函数直接计算工作日差？** > 无直接函数，但可通过`DATEFIRST`设置周起始日结合`DATEPART(dw)`简化周末判断（依赖服务器配置）[^2]。 4. **大日期范围（如10年）的性能如何优化？** > 使用预生成的数字表（非系统表）替代`sys.objects`，或改用`VALUES`子句分块生成序列。 [^1]: WITH AS短语用于提高SQL可读性，但本题禁止使用CTE。 [^2]: 日期函数依赖服务器配置，模7法可确保跨环境一致性。 [^3]: 递归查询在SQL Server需用CTE实现，但本题要求规避此语法。