求解oracle数据库一条sql查询出来 10C

Oracle数据库表a有area,value,date三个字段,存放着两个地区每日的统计数据:
Area Value date
海淀 12 2012-01-01
朝阳 19 2012-01-01
海淀 14 2012-01-02

请写出一条SQL语句得到如下格式结果:
“日期、海淀统计数、朝阳统计数、总数”
如:2012-01-01、12、19、31

10个回答

select
date,
nvl(sum(case when area='海淀' then value end),0) as 海淀统计数,
nvl(sum(case when area='朝阳' then value end),0) as 朝阳统计数,
nvl(sum(value)) as 总数
from table
group by date;

r471508128
r471508128 回复weixin_37306052: 你的表名是table吗?
大约 2 年之前 回复
weixin_37306052
weixin_37306052 为什么我执行是报缺少左括号? 截图在底下
大约 2 年之前 回复
z13571836771
就是不会 总数少个0;nvl(sum(value),0) as 总数
大约 2 年之前 回复
r471508128
r471508128 写的不错,很完整
大约 2 年之前 回复

select date,(select sum(Value) from table where area="海淀") as 海淀统计数,(select sum(Value) from table where area="朝阳") as 朝阳统计数

,sum(Value) as '总数' from table

 select a.date,(select sum(b.Value) from table b where b.area="海淀" and b.date=a.date ) as 海淀统计数,(select sum(c.Value) from table c where c.area="朝阳" and c.date=a.date) as 朝阳统计数

,(select sum(d.Value) from table d where d.date=a.date ) as '总数' from table a group by a.date
 select a.date as '日期',(select sum(b.Value) from table b where b.area="海淀" and b.date=a.date ) as '海淀统计数',(select sum(c.Value) from table c where c.area="朝阳" and c.date=a.date) as '朝阳统计数'

,(select sum(d.Value) from table d where d.date=a.date ) as '总数' from table a group by a.date

select date, sum(海淀统计数) as 海淀统计数, sum(朝阳统计数) as 朝阳统计数, sum(总数) as 总数 from
(
select date,sum(Value) as 海淀统计数,0 as 朝阳统计数, sum(Value) as '总数' from table where area="海淀"
union all
select date,0 as 海淀统计数,sum(Value) as 朝阳统计数, sum(Value) as '总数' from table where area="朝阳"
) a
group by date

nvl(sum(case when area='海淀' then value end),0) as 海淀统计数,
nvl(sum(case when area='朝阳' then value end),0) as 朝阳统计数,

select date as"日期",c.value as "海定统计数”,b.value as "朝阳统计数,count(*) as "总数”
from a as "c",a as "b"
group by area

图片说明为什么缺少做括号

create table test03 (vdate date,area varchar2(30),vvalue int)
insert into test03 values (trunc(sysdate,'d'),'haidian',10);
insert into test03 values (trunc(sysdate,'d'),'haidian',10);
insert into test03 values (trunc(sysdate,'d'),'chaoyang',10);
insert into test03 values (trunc(sysdate,'d')-1,'chaoyang',10);
insert into test03 values (trunc(sysdate,'d')-1,'haidian',10);
insert into test03 values (trunc(sysdate,'d')-1,'chaoyang',10);
insert into test03 values (trunc(sysdate,'d')-1,'chaoyang',10);

select d.*, d.haidian + d.chaoyang sums
from (select *
from test03
pivot(sum(vvalue)
for area in('haidian' haidian, 'chaoyang' chaoyang))) d

oracle里行转列,列转行一般都有pivot或者unpivot,旋转函数很好用,性能也不错,如果你使用的数据库不支持这些函数再考虑使用前面的那些方案~

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐