有一个表结构为:
GC_CODE PLAN_DATE PLAN_QTY
A 20121010 10
A 20121010 50
A 20121020 100
A 20130101 30
B 20121001 20
B 20121012 1000
B 20121123 200
B 20121201 10
C 20130101 1500
D 20111201 2000想得到结果是在20121010至20121231这段时间内,存在PLAN_QTY>0的GC_CODE,以及这些GC_CODE在这个时间段内最早的PLAN_DATE,和在这天的PLAN_QTY合计数。结果应该为:
A 20121010 60
B 20121012 1000谢谢!
select GC_CODE, PLAN_DATE, PLAN_QTY from (
select GC_CODE, PLAN_DATE, PLAN_QTY,
row_number() over (partition by GC_CODE order by PLAN_DATE) rn from (
select GC_CODE, PLAN_DATE, sum(PLAN_QTY) PLAN_QTY from t
where PLAN_QTY>0 and PLAN_DATE between 20121010 and 20121231
group by GC_CODE, PLAN_DATE order by 1, 2)
) where rn=1;
Select T.GC_Code,T.PLAN_DATE,SUM(T.PLAN_QTY) From test T
,
(select B.GC_Code,Min(B.PLAN_DATE) as PLAN_DATE From
(SELECT GC_CODE, PLAN_DATE
FROM test
WHERE (PLAN_DATE BETWEEN 20121010 AND 20121231)
GROUP BY GC_CODE, PLAN_DATE) B
group by GC_CODE) C
where T.GC_CODE=C.GC_CODE And T.PLAN_DATE=C.PLAN_DATE
Group By T.GC_Code,T.PLAN_DATE
from test1 a,
(select min(plan_date) plan_date, gc_code gc_code
from test1
where plan_date between 20121010 and 20121231
group by gc_code) b where a.plan_date = b.plan_date
group by a.gc_code, b.plan_date