如何将Excel操作应用于SQLServer查询？

我当前正在使用SSMS2008。

我想使用SSMS完成操作，并在Excel屏幕快照中进行了描述。

我连接了两个表，一个表对雇员开始工作的时间有一个正数，而另一个表对雇员离职的时间有一个负数。我正在寻找一列显示每小时员工人数的列。
感谢您对此事的帮助，
谢谢，

它正在运行，可以使用windowed SUM来实现：

SELECT *,SUM(Employee) OVER(ORDER BY [Date],[Time]) as Total_available
FROM tab
ORDER BY [Date],[Time];

,

SQL Fiddle

MS SQL Server 2017架构设置：

CREATE TABLE MyTable (Dates Date,Times Time,EmployeesAvailable int)
INSERT INTO MyTable (Dates,Times,EmployeesAvailable) VALUES('2019-11-01','08:00',2)
INSERT INTO MyTable (Dates,'09:00',5)
INSERT INTO MyTable (Dates,'10:00',3)
INSERT INTO MyTable (Dates,'12:00',-5)
INSERT INTO MyTable (Dates,'13:00','14:00',-5)


查询1 ：

SELECT Dates,EmployeesAvailable,SUM(EmployeesAvailable) OVER(ORDER BY Dates,Times) AS 'Total Available'
FROM MyTable


Results ：

| Dates | Times | EmployeesAvailable | Total Available |
|------------|------------------|--------------------|-----------------|
| 2019-11-01 | 08:00:00.0000000 | 2 | 2 |
| 2019-11-01 | 09:00:00.0000000 | 5 | 7 |
| 2019-11-01 | 10:00:00.0000000 | 3 | 10 |
| 2019-11-01 | 12:00:00.0000000 | -5 | 5 |
| 2019-11-01 | 13:00:00.0000000 | 2 | 7 |
| 2019-11-01 | 14:00:00.0000000 | -5 | 2 |

,

SUM OVER的另一种方法是自连接，其聚合值较低或相等。

样本数据：

CREATE TABLE TestEmployeeRegistration (
[Date] DATE,[Time] TIME,[Employees] INT NOT NULL DEFAULT 0,PRIMARY KEY ([Date],[Time])
);
INSERT INTO TestEmployeeRegistration
([Date],[Time],[Employees]) VALUES
('2019-11-01',2),('2019-11-01',5),3),-5),-5);


查询：

SELECT t.[Date],t.[Time],t.[Employees],SUM(t2.[Employees]) AS [Total available]
FROM [TestEmployeeRegistration] t
JOIN [TestEmployeeRegistration] t2
ON t2.[Date] = t.[Date]
AND t2.[Time] <= t.[Time]
GROUP BY t.[Date],t.[Employees]
ORDER BY t.[Date],t.[Time];


使用SUM的窗口函数时，我建议按“日期”进行分区。

SELECT *,SUM([Employees]) OVER (PARTITION BY [Date] ORDER BY [Time]) AS [Total available]
FROM [TestEmployeeRegistration]
ORDER BY [Date],[Time];


在妊娠here上进行的测试