SQL Server 2016最大分区中的select语句不起作用
问题描述:
我试图通过语句分区返回分组集的最小和最大日期时间。分钟线有效,但最大值返回未分组结果的最大日期。 SQL Server 2016.我错过了什么?SQL Server 2016最大分区中的select语句不起作用
SELECT
[temp_Emp], [temp_EmpID], [temp_Date], [Temp_Start], [Temp_End],
MIN(Temp_Start) OVER (PARTITION BY temp_EmpID, temp_Date ORDER BY Temp_Start, Temp_End) AS 'Start',
MAX(Temp_End) OVER (PARTITION BY temp_EmpID, temp_Date ORDER BY Temp_Start, Temp_End) AS 'End'
FROM
tmp_startend
结果:
temp_EmpID temp_Date Temp_Start Temp_End Start End
300 7/08/2017 7/08/2017 8:00 7/08/2017 8:15 7/08/2017 8:00 7/08/2017 8:15
300 7/08/2017 7/08/2017 8:15 7/08/2017 8:30 7/08/2017 8:00 7/08/2017 8:30
300 7/08/2017 7/08/2017 9:00 7/08/2017 10:00 7/08/2017 8:00 7/08/2017 10:00
300 7/08/2017 7/08/2017 9:15 7/08/2017 14:30 7/08/2017 8:00 7/08/2017 14:30
300 7/08/2017 7/08/2017 9:30 7/08/2017 14:00 7/08/2017 8:00 7/08/2017 14:30
300 7/08/2017 7/08/2017 10:00 7/08/2017 13:00 7/08/2017 8:00 7/08/2017 14:30
300 7/08/2017 7/08/2017 10:00 7/08/2017 14:30 7/08/2017 8:00 7/08/2017 14:30
300 7/08/2017 7/08/2017 14:00 7/08/2017 15:00 7/08/2017 8:00 7/08/2017 15:00
答
离开了order by
。这使得值累计:
SELECT [temp_Emp], [temp_EmpID],[temp_Date],[Temp_Start],[Temp_End],
min(Temp_Start) over (partition by temp_EmpID, temp_Date) as [Start],
max(Temp_End) over (partition by temp_EmpID, temp_Date) as [End]
FROM tmp_startend;
的min()
刚刚发生的工作,因为order by
把最小值第一 - 所以累积min()
相同的整体min()
。
谢谢你,我尝试删除顺序,但我得到一个错误,说分区需要一个命令,我必须把错误的格式。 – Dave