在数据查询过程中本人遇到下面这样的问题,现在请教一下各位高手,希望大家帮个忙,
有三个不同的查询语句
1:
select year_mon,sum(gasmon)
from a
where id='HBsRf0t6UI'
and class=11
group by year_mon
2:
select year_mon,sum(wellgasmon)
from b
where id='HBsRf0t6UI'
and class=11
group by year_mon
3:
select year_mon,sum(gasprodmon)
from c
where id='HBsRf0t6UI'
and class=11
group by year_mon本人希望能显示出下面的效果:
year_mon sum(gasmon) sum(wellgasmon) sum(gasprodmon)
200702 122 222 123
200703 333 234 342
200704 0 2334 0
200705如果哪个月没有数据就用 0 填充
希望各位大哥大姐帮个忙,在想感激不尽~
有三个不同的查询语句
1:
select year_mon,sum(gasmon)
from a
where id='HBsRf0t6UI'
and class=11
group by year_mon
2:
select year_mon,sum(wellgasmon)
from b
where id='HBsRf0t6UI'
and class=11
group by year_mon
3:
select year_mon,sum(gasprodmon)
from c
where id='HBsRf0t6UI'
and class=11
group by year_mon本人希望能显示出下面的效果:
year_mon sum(gasmon) sum(wellgasmon) sum(gasprodmon)
200702 122 222 123
200703 333 234 342
200704 0 2334 0
200705如果哪个月没有数据就用 0 填充
希望各位大哥大姐帮个忙,在想感激不尽~
from a,b,c
where a.id=b.id and a.id=c.id
and a.class=b.class and a.class=c.class
and a.id='HBsRf0t6UI'
and a.class=11
group by a.year_mon
然后和这三张表做外关联查询
from a
where id='HBsRf0t6UI'
and class=11
group by year_mon nvl用来判断是否为空
如果没有最后条
200705
那三张表做UNION
再SUM一下就行了
from a
inner join b on a.year_mon=b.year_mon
inner join c on b.year_mon=c.year_mon
where (a.id='HBsRf0t6UI' and b.id='HBsRf0t6UI' and c.id='HBsRf0t6UI' )
and (a.class=11 and b.class=11 and c.class=11 )
group by year_mon
year_mon sum(gasmon) sum(wellgasmon) sum(gasprodmon)
200702 122 222 123
200703 333 234 342
200704 0 2334 0
200705 324 2342 234这样的效果也行。楼上的方法 本人试了一下 和单个查询语句统计出来的结果不一样
FROM (SELECT year_mon, SUM (gasmon) gasmon, 0 wellgasmon, 0 gasprodmon
FROM a
WHERE ID = 'HBsRf0t6UI' AND CLASS = 11
GROUP BY year_mon
UNION ALL
SELECT year_mon, 0, SUM (wellgasmon), 0
FROM b
WHERE ID = 'HBsRf0t6UI' AND CLASS = 11
GROUP BY year_mon
UNION ALL
SELECT year_mon, 0, 0, SUM (gasprodmon)
FROM c
WHERE ID = 'HBsRf0t6UI' AND CLASS = 11
GROUP BY year_mon)
GROUP BY year_mon