能否在SQL Server查询结果行上对HH_POINTAGE列执行指定算术运算？

你的需求本质是对同一天同一员工的**每一组进出记录（S对应进入、E对应离开）**计算时长差，再把所有差值加总。下面提供两种实用的实现方式，你可以根据场景选择：

方法1：自连接配对进出记录 #

先把你原有的查询封装成子查询，再将标记为S的奇数行和对应的E偶数行（行号=奇数行号+1）做自连接，计算每对的差值后求和：

WITH PointageDetails AS (
    SELECT 
        MATRICULE, 
        DT_POINTAGE, 
        HH_POINTAGE, 
        ROW_NUMBER() OVER (PARTITION BY MATRICULE, DT_POINTAGE ORDER BY MATRICULE, DT_POINTAGE) AS NEMRA, 
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY MATRICULE, DT_POINTAGE ORDER BY MATRICULE, DT_POINTAGE) % 2 = 1 THEN 'S' ELSE 'E' END AS ENTREE_SORTIE 
    FROM POINTAGES 
    WHERE MATRICULE = '0118' 
      AND DT_POINTAGE = '30/10/2019' 
      AND HH_POINTAGE NOT IN (
          (SELECT MAX(HH_POINTAGE) FROM POINTAGES WHERE MATRICULE = '0118' AND DT_POINTAGE = '30/10/2019'), 
          (SELECT MIN(HH_POINTAGE) FROM POINTAGES WHERE MATRICULE = '0118' AND DT_POINTAGE = '30/10/2019')
      )
)
SELECT 
    SUM(e.HH_POINTAGE - s.HH_POINTAGE) AS TotalDifference
FROM PointageDetails s
JOIN PointageDetails e ON s.MATRICULE = e.MATRICULE 
                      AND s.DT_POINTAGE = e.DT_POINTAGE 
                      AND s.NEMRA + 1 = e.NEMRA
WHERE s.ENTREE_SORTIE = 'S' 
  AND e.ENTREE_SORTIE = 'E';

方法2：用LAG窗口函数简化计算 #

这种方式不需要自连接，直接用LAG()函数获取上一行的HH_POINTAGE值，当当前行是E时计算差值，最后求和：

WITH PointageDetails AS (
    SELECT 
        MATRICULE, 
        DT_POINTAGE, 
        HH_POINTAGE, 
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY MATRICULE, DT_POINTAGE ORDER BY MATRICULE, DT_POINTAGE) % 2 = 1 THEN 'S' ELSE 'E' END AS ENTREE_SORTIE,
        LAG(HH_POINTAGE) OVER (PARTITION BY MATRICULE, DT_POINTAGE ORDER BY MATRICULE, DT_POINTAGE) AS PreviousHH
    FROM POINTAGES 
    WHERE MATRICULE = '0118' 
      AND DT_POINTAGE = '30/10/2019' 
      AND HH_POINTAGE NOT IN (
          (SELECT MAX(HH_POINTAGE) FROM POINTAGES WHERE MATRICULE = '0118' AND DT_POINTAGE = '30/10/2019'), 
          (SELECT MIN(HH_POINTAGE) FROM POINTAGES WHERE MATRICULE = '0118' AND DT_POINTAGE = '30/10/2019')
      )
)
SELECT 
    SUM(CASE WHEN ENTREE_SORTIE = 'E' THEN HH_POINTAGE - PreviousHH ELSE 0 END) AS TotalDifference
FROM PointageDetails;

小提醒 #

两种方法都假设你的记录是严格按S→E→S→E的顺序排列的（行号奇偶性完全对应进出顺序），如果存在异常数据（比如连续两个S或E），结果会不准确，需要先校验数据完整性。

内容的提问来源于stack exchange，提问作者Soufiane.Ach