表A,使用到的字段ID,时间字段TM,值DRPselect ID,sum(drp) from A
where tm<to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-12-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID出来的列表为
ID,SUM(DRP)
1 0
2 10
3 20
........select ID,sum(drp) from A
where tm<to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-11-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID出来的列表为
ID,SUM(DRP)
1 10
2 20
4 40
..........现求一SQL,能够满足以下列表的结果,即按ID将以上两句SQL的值合并到列
ID,SUM(DRP) M1,SUM(DRP) M2
1 0 10
2 10 20
3 20
4 40
.................
where tm<to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-12-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID出来的列表为
ID,SUM(DRP)
1 0
2 10
3 20
........select ID,sum(drp) from A
where tm<to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-11-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID出来的列表为
ID,SUM(DRP)
1 10
2 20
4 40
..........现求一SQL,能够满足以下列表的结果,即按ID将以上两句SQL的值合并到列
ID,SUM(DRP) M1,SUM(DRP) M2
1 0 10
2 10 20
3 20
4 40
.................
WHERE b.ID(+)=c.ID
AND b.tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
AND b.tm>=to_date('2009-12-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
AND c.tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
AND c.tm>=to_date('2009-11-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
GROUP BY c.ID;另外你给出的样例数据有问题,
m1应该是M2的子集。
select ID,sum(drp) M1,'' AS M2 from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-11-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID
UNION
select ID,'' AS M1 ,sum(drp) M2 from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-12-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID
suiziguo的回答执行时,一直执行不完,只好强行中止了。
oraclemch的回答还需要仔细消化一下,直接给SQL就好了。多谢各位。
(select ID,sum(drp) a_drp from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-12-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID) a,
(select ID,sum(drp) b_drp from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-11-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID) b
where a.id=b.id
select ID, sun(M1), SUN(M2) FROM
(
select ID,sum(drp) M1,0 AS M2 from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-11-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID
UNION
select ID,0 AS M1 ,sum(drp) M2 from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-12-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID
) T
GROUP BY T.ID
(select ID,drp m1,0 m2 from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-12-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID
union all
select ID,0 m1,drp m2 from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-11-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID) b
group by b.id;
SELECT NVL(V1.ID, V2.ID2) AS ID, M1, M2
FROM (select ID, sum(drp) M1
from A
where tm < to_date('2010-1-25 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tm >= to_date('2009-12-25 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by ID) V1
FULL OUTER JOIN (select ID, sum(drp) M2
from A
where tm < to_date('2010-1-25 10:00:00',
'yyyy-mm-dd hh24:mi:ss')
and tm >= to_date('2009-11-25 10:00:00',
'yyyy-mm-dd hh24:mi:ss')
group by ID) V2
USING (ID)
如果我没看错题的话,这样写应该就可以了
select ID,
sum(case when tm>=to_date('2009-12-25 10:00:00','yyyy-mm-dd hh24:mi:ss') then drp end)m1
,sum(drp)m2 from A
where tm <to_date('2010-1-25 10:00:00','yyyy-mm-dd hh24:mi:ss') and tm>=to_date('2009-11-25 10:00:00','yyyy-mm-dd hh24:mi:ss')
group by ID
7楼,9楼速度相似,但7楼的结果集少了20多条记录。
FULL OUTER JOIN貌似可以,但无法执行,可能需要修改一下。
A表大约1000万条数据,TM上有索引,也有表分区。
谢谢各位了。长了不少知识。