在SQL

Question

从和窗口功能不包括第一次和最后一行

我执行下面的查询

SELECT QLID, Emp_Date, Emp_Time, sum(emp_time) over(partition by qlid order by emp_time) 
FROM GCC_INTERNAL_OPS.Entry_Time where (qlid='fs255015' or qlid='mf255005') and emp_date=date '2013-01-31' 

返回下面的结果集

QLID Emp_Date Emp_Time Group Sum(Emp_Time) 
FS255015 01/31/2013 1218 5390.00 
FS255015 01/31/2013 1523 5390.00 
FS255015 01/31/2013 1526 5390.00 
FS255015 01/31/2013 1123 5390.00 
MF255005 01/31/2013 0932 15033.00 
MF255005 01/31/2013 0936 15033.00 
MF255005 01/31/2013 1106 15033.00 
MF255005 01/31/2013 1332 15033.00 
MF255005 01/31/2013 1338 15033.00 
MF255005 01/31/2013 1348 15033.00 
MF255005 01/31/2013 1509 15033.00 
MF255005 01/31/2013 1544 15033.00 
MF255005 01/31/2013 1617 15033.00 
MF255005 01/31/2013 1639 15033.00 
MF255005 01/31/2013 1732 15033.00 

我要排除的第一个和最后一排每个qlid的总和。任何想法，我将如何做到这一点？

Answer 1

尝试：

;with cte as 
(SELECT e.*, 
     row_number() over(partition by qlid order by emp_time) rna, 
     row_number() over(partition by qlid order by emp_time desc) rnd 
FROM Entry_Time e 
where (qlid='fs255015' or qlid='mf255005') and emp_date='2013-01-31') 
SELECT QLID, 
     Emp_Date, 
     Emp_Time, 
     sum(case when 1 in (rna,rnd) then 0 else emp_time end) 
      over(partition by qlid) conditional_sum 
FROM cte 

SQLFiddle here。