使用区划表去Left join你的橘子表,苹果表 最后按照区划做group by 然后各个表的数量取sum就OK了
select AB.地区, 苹果, 橘子, 香蕉 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 AB.地区, 苹果, 橘子, 香蕉, sum(苹果+橘子+香蕉) 小计 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.地区;
with app as ( 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 然后各个表的数量取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后怎么可能重复?