解决方案 »
- oracle 行列转化
- SQL语句翻译成ORACLE语句
- oracle 时间类型
- 100分只求只有一个表的SQL语句
- 怎么知道两个日期date变量之间相差多少年,月,日,时,分,秒?
- 如何在本机的Oracle控制台上操作远程的Oracle数据库实例
- 请问各位老师oracle有没有类似于server sql 2000数据库复制技术 实现数据同步更新的功能?
- makefile问题:如何使.pc程序允许使用//注释
- 我的sql/plus 为什么不能执行 select * from xxx???????
- oracle 对系统资源的要求至少是什么配置呢?
- oracle 批量更新数据 月份中日的值必须介于 1 和当月最后一日之间
- 大四学生适合学ocp吗?要有经验吗?
最后按照区划做group by 然后各个表的数量取sum就OK了
from
(select A.地区, 苹果, 橘子
from
(select 地区, count(*) 苹果
from 苹果表
group by 地区) A
full outer join
(select 地区, count(*) 橘子
from 橘子表
group by 地区) B
on A.地区 = B.地区) AB
full outer join
(select 地区, count(*) 香蕉
from 香蕉表
group by 地区) C
on AB.地区 = C.地区;
from
(select A.地区, 苹果, 橘子
from
(select 地区, count(*) 苹果
from 苹果表
group by 地区) A
full outer join
(select 地区, count(*) 橘子
from 橘子表
group by 地区) B
on A.地区 = B.地区) AB
full outer join
(select 地区, count(*) 香蕉
from 香蕉表
group by 地区) C
on AB.地区 = C.地区;
select 'A01' AS str,'12' as str2 FROM DUAL
UNION ALL
select 'A02' AS str,'22' as str2 FROM DUAL
UNION ALL
select 'A03' AS str,'32' as str2 FROM DUAL
),
orj as (
select 'A01' AS str,'14' as str2 FROM DUAL
UNION ALL
select 'A02' AS str,'24' as str2 FROM DUAL
UNION ALL
select 'A04' AS str,'34' as str2 FROM DUAL
),
ban as (
select 'A01' AS str,'16' as str2 FROM DUAL
UNION ALL
select 'A02' AS str,'26' as str2 FROM DUAL
UNION ALL
select 'A05' AS str,'36' as str2 FROM DUAL
),
area as (
select 'A01' AS str FROM DUAL
UNION ALL
select 'A02' AS str FROM DUAL
UNION ALL
select 'A03' AS str FROM DUAL
UNION ALL
select 'A04' AS str FROM DUAL
UNION ALL
select 'A05' AS str FROM DUAL
)
SELECT nvl(area.str, 'Total') as str,
sum(nvl(app.str2, 0)) as appstr2,
sum(nvl(orj.str2, 0)) as orjstr2,
sum(nvl(ban.str2, 0)) as banstr2,
sum(nvl(app.str2, 0) + nvl(orj.str2, 0) + nvl(ban.str2, 0)) as subtotal
from area
left join app on area.str = app.str
left join orj on area.str = orj.str
left join ban on area.str = ban.str
group by rollup(area.str)
不可能,肯定还是你的语句有错误,既然都做了group by还重复?
把语句贴出来
你说的结果都违背了GROUP BY的语法含义了,GROUP BY后怎么可能重复?