数据表T1:
Category Dept Sales
A 1 100
A 2 100
A 3 100
A 4 100
B 5 100
B 2 100
B 3 100
B 4 100
B 1 100对照表T2:
Category Dept
A 1
A 2
B 2
B 3如何查出表T1中的Dept在T2中的Sales,然后不在的,求和记为“Others”
得到的结果如下:Category Dept Sales
A 1 100
A 2 100
A Others 200
B 2 100
B 3 100
B Others 300
Category Dept Sales
A 1 100
A 2 100
A 3 100
A 4 100
B 5 100
B 2 100
B 3 100
B 4 100
B 1 100对照表T2:
Category Dept
A 1
A 2
B 2
B 3如何查出表T1中的Dept在T2中的Sales,然后不在的,求和记为“Others”
得到的结果如下:Category Dept Sales
A 1 100
A 2 100
A Others 200
B 2 100
B 3 100
B Others 300
select a.* from t1 a,t2 b where a.Category =b.Category and a.Dept =b.dept
union all
select Category,'Others',sum(Sales) from (select * from t1 a where not exists(select Dept from from t2 b where a.Category =b.Category and a.Dept =b.dept) group by Category
select a.* from tt1 a,tt2 b where a.Category =b.Category and a.Dept =b.dept
union all
select Category,'Others',sum(Sales) from (select * from tt1 a where not exists(select Dept from tt2 b where a.Category =b.Category and a.Dept =b.dept)) group by Category
已测试A 1 100
A 2 100
B 2 100
B 3 100
B Others 300
A Others 200
from t1,
t2
where t1.dept = t2.dept(+)
and t1.Category = t2.Category(+)
group by nvl(t2.dept,'others'),t1.Category