如何修改这条SQL语句才能实现所要求的效果~~急~~~~
select Begdate ,Enddate, sum(Unckqty) as Unckqty from(
SELECT trunc(
((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24) ) Begdate ,
trunc(((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)+3 ) Enddate,
COUNT (Vendor) Unckqty FROM table1 WHERE (Cfm_status=0)
GROUP BY
((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24),
((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)+3
)
WHERE Begdate> =24
GROUP BY Begdate ,Enddate
ORDER BY Begdate
得到结果：
Begdate Enddate Unckqty
24 27 1
25 28 1
26 29 3
27 30 34
28 31 12
... .... .....
要求得到：
Begdate Enddate Unckqty
24 27 a
28 31 b
32 35 ...
... .... .....
其中 a+b=1+1+3+34+12
求SQL語句~~~~
[解决办法]
从你的描述来看，你的需求是在当前时刻的24小时以前，求每四个小时Unckqty的合计数。
如果我没有理解错误的话，可以发现只需要按照开始时间做每四个小时的分组就可以了，分组条件加上Enddate没有什么意义,只需要加上3个小时表示一个时间就可以了。
你可以试试下面的语句：
SELECT Begdate,Enddate, sum(Unckqty) as Unckqty from (
SELECT TRUNC(((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*6))*4 Begdate ,
TRUNC( ((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*6)) *4 + 3 Enddate,
COUNT (Vendor) Unckqty FROM TABLE1 WHERE (Cfm_status=0)
GROUP BY TRUNC((sysdate-(CASE WHEN (Upd_date= ' ' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*6) *4
) WHERE Begdate> =24
group by Begdate,Enddate ORDER BY Begdate ;
