你的结果点问题吧,01订单'B35'分摊到其他商品上是60/3=20吧:with t as
(select '01' bill, 100 amount, 'D100' goods, 'N' tax_no
from dual
union all
select '01' bill, 50 amount, 'D300' goods, 'N' tax_no
from dual
union all
select '01' bill, 70 amount, 'D200' goods, 'N' tax_no
from dual
union all
select '01' bill, 60 amount, 'B35' goods, '17' tax_no
from dual
union all
select '02' bill, 60 amount, 'D300' goods, 'N' tax_no
from dual
union all
select '02' bill, 80 amount, 'D100' goods, 'N' tax_no
from dual
union all
select '02' bill, 90 amount, 'B35' goods, '17' tax_no
from dual
union all
select '03' bill, 30 amount, 'D100' goods, 'N' tax_no
from dual
union all
select '03' bill, 40 amount, 'D300' goods, 'N' tax_no from dual)
SELECT BILL,
AMOUNT,
AMOUNT P_AMOUNT,
nvl((SELECT SUM(T2.amount)
FROM T T2
WHERE T2.BILL = T1.BILL
AND T2.GOODS = 'B35') /
(SELECT COUNT(*)
FROM T T2
WHERE T2.BILL = T1.BILL
AND T2.GOODS <> 'B35'),
0) tax,
t1.goods,
nvl((SELECT distinct tax_no
FROM T T2
WHERE T2.BILL = T1.BILL
AND T2.GOODS = 'B35'),
t1.tax_no) tax_no
FROM T T1;
(select '01' bill, 100 amount, 'D100' goods, 'N' tax_no
from dual
union all
select '01' bill, 50 amount, 'D300' goods, 'N' tax_no
from dual
union all
select '01' bill, 70 amount, 'D200' goods, 'N' tax_no
from dual
union all
select '01' bill, 60 amount, 'B35' goods, '17' tax_no
from dual
union all
select '02' bill, 60 amount, 'D300' goods, 'N' tax_no
from dual
union all
select '02' bill, 80 amount, 'D100' goods, 'N' tax_no
from dual
union all
select '02' bill, 90 amount, 'B35' goods, '17' tax_no
from dual
union all
select '03' bill, 30 amount, 'D100' goods, 'N' tax_no
from dual
union all
select '03' bill, 40 amount, 'D300' goods, 'N' tax_no from dual)
SELECT BILL,
AMOUNT,
AMOUNT P_AMOUNT,
nvl((SELECT SUM(T2.amount)
FROM T T2
WHERE T2.BILL = T1.BILL
AND T2.GOODS = 'B35') /
(SELECT COUNT(*)
FROM T T2
WHERE T2.BILL = T1.BILL
AND T2.GOODS <> 'B35'),
0) tax,
t1.goods,
nvl((SELECT distinct tax_no
FROM T T2
WHERE T2.BILL = T1.BILL
AND T2.GOODS = 'B35'),
t1.tax_no) tax_no
FROM T T1;
解决方案 »
- Oracle中怎么批量调整显示结果的列宽,不要column ** format a12;这样的答复~
- Oracle Database 11g(11.1.0.7.0)(第1版) perl.exe进程占用?
- Suse linux 11g sp1+Oracle 11 g R2+RAC的EM配置问题
- sql语句错在哪里?
- 关于oracle服务的2个问题
- group by分组的一个问题
- 什么是事务应用?
- 关于ORACLE9I的透明网关的总是。!!!
- 求在本机连其他主机上的ORACLE数据库的方法(详见内)
- toadforOracle安装请教
- 初当版主,散分同庆,请大家以后多多关照哈!!!
- OracleBulkCopy数据重复导致索引失效
money,
money1,
nvl(money2, 0) / numb tax,
nvl2(money2, taxcode1, taxcode) taxcode
from (select t1.orderid,
t1.money,
t1.money money1,
t1.commodity,
t2.money money2,
t3.numb,
t2.taxcode taxcode1,
t1.taxcode
from temp t1,
(select orderid, money, taxcode
from temp
where commodity = 'B35') t2,
(select orderid, count(*) numb
from temp
where commodity <> 'B35'
group by orderid) t3
where t1.commodity <> 'B35'
and t1.orderid = t2.orderid(+)
and t3.orderid = t1.orderid
order by t1.orderid, t1.commodity) t4