每一个SQL语句要返回相同的列(列不足的可以用常数补满),而且合并列类型要相互兼容 然后用UNION 进行合并就可以了
比如:
select 4 from dual union select 5 from dual;
4
----------
4
5
合并两个集合
比如:
select 4 from dual union select 5 from dual;
4
----------
4
5
合并两个集合
解决方案 »
- 触发器中如何取消插入操作
- Oracle初学者:win7上装了一个Oracle g11.之后试着连接,就遇到了ORA-12560: TNS: 协议适配器错误- Oracle
- vs2005 c++编译occi报 error LNK2019: 无法解析的外部符号
- oracle 触发器问题(精)
- 摆脱各位大侠帮忙啊!C++开发Oracle的问题
- 谁能清楚明白地解答客户端的tnsnames.ora和服务器端的tnsnames.ora中配置的服务的区别,谢谢先?
- 为什么我用IMP导入的时候出现"1658"错误?急,在线等.
- 请问,这个过程编译为什么有这个错误?
- oracle中如何存储图片
- 求满足条件的行数
- 新手恳请各位大狭帮忙建表_________creat!
- bdump没有"分布式 死锁信息"
note1的结果
receipt_money dept_id
10000 8
20000 9
30000 7
note2的结果
issue_money dept_id
50000 11
60000 12
70000 13那我合并后的结果想得到
receipt_money issue_money dept_id
10000 0 8
20000 0 9
30000 0 7
0 50000 11
0 60000 12
0 70000 13
note1
select sum(round(t.qty*t.unit_price,2)) as receipt_money,0 as issue_money,t.dept_id from mm_operation_history t
where t.bill_type in (1,14)
--and t.operation_date>= to_date('2006-09', 'yyyy-mm')
--and t.operation_date<= add_months(to_date('2006-09', 'yyyy-mm'), 1) - 1
group by t.dept_id;
--note2
select 0 as receipt_money,sum(round(t.qty*t.unit_price,2)) as issue_money,t.dept_id from mm_operation_history t
where t.bill_type = 2
group by t.dept_id;
where t.bill_type in (1,14)
group by t.dept_id
union
select 0 as receipt_money,sum(round(t.qty*t.unit_price,2)) as issue_money,t.dept_id from mm_operation_history t
where t.bill_type = 2
group by t.dept_id;
你这样执行看看!!
A 1
B 2
select 2 結果︰
A 1
B 1union :
A 1
B 2
B 1union all :
A 1
B 2
A 1
B 1minus:
B 2intersect:
A 1
不知道这样能否看懂
select 1 結果︰
column1 dept_id
any1 1
any1 2
select 2 結果︰
column2 dept_id
any2 1
any2 1
select 2 結果︰
column3 dept_id
any3 1
any3 1能不能得到
column1 column2 column3 dept_id
any1 any2 any3 1
any1 0 0 2
你的dept_id来对应
try:select nvl(a.column1,0),nvl(b.column2,0),nvl(c.column3,0),d.dept_id
from tb_dept d,tb1 a,tb2 b,tb3 c
where d.dept_id=a.dept_id(+)
and d.dept_id=b.dept_id(+)
and d.dept_id=c.dept_id(+);
(select sum(round(t.qty*t.unit_price,2)) from mm_operation_history t
where t.bill_type in (1,14) and t.dept_id=m.dept_id ) as oneMoney,
(select sum(round(t.qty*t.unit_price,2)) from mm_operation_history t
where t.bill_type = 2 and t.dept_id=m.dept_id ) as twoMoney,
(select sum(round(t.qty*t.unit_price,2)) from mm_operation_history t
where t.bill_type = 3 and t.dept_id=m.dept_id) as threeMoney,
(select sum(round(t.qty*t.unit_price,2)) from mm_operation_history t
where t.bill_type=6 and t.dept_id=m.dept_id) as fourMoney,
(select sum(round(t.qty*t.unit_price,2)) from mm_operation_history t
where t.bill_type=5 and t.dept_id=m.dept_id) as fiveMoney,
( select sum(round(t.qty*t.unit_price,2)) from mm_operation_history t where t.operation_history_id in(
select max(t.operation_history_id) from mm_operation_history t
group by t.material_id, t.project_id ) and t.dept_id=m.dept_id) as sixMoney,m.dept_id
from mm_operation_history m
where m.operation_date>= to_date('2006-09', 'yyyy-mm')
and m.operation_date<= add_months(to_date('2006-09', 'yyyy-mm'), 1) - 1
group by m.dept_id
重新拼了下,似乎得到了想要的结果,但是 性能真让人汗颜, 竟然要 6.325s
谁能给优化下或者给出更好的解决方法, 不盛感激