有3張表:
A 表 B 表 C 表
A1 A2 A3 B1 B2 B3 C1 C2 C3 C4
BAN CAR001 55 BAN CAR001 56 BAN CAR001 07pM AA
BAN CAR007 73 BAN CAR911 35 BAN CAR001 07pM BB
BAF CAR105 12 BAF CAR105 15 BAN CAR001 07pL CC
BAN CAR911 32 BAF CAR105 01pH DF
BAN CAR007 08pB CE
我要的到的結果是:
D
D1 D2 D3 D4 D5 D6
BAN CAR001 55 56 07pM AA
-- -- -- -- 07pM BB
-- -- -- -- 07pL CC
BAN CAR007 73 0 08pB CE
A 表 B 表 C 表
A1 A2 A3 B1 B2 B3 C1 C2 C3 C4
BAN CAR001 55 BAN CAR001 56 BAN CAR001 07pM AA
BAN CAR007 73 BAN CAR911 35 BAN CAR001 07pM BB
BAF CAR105 12 BAF CAR105 15 BAN CAR001 07pL CC
BAN CAR911 32 BAF CAR105 01pH DF
BAN CAR007 08pB CE
我要的到的結果是:
D
D1 D2 D3 D4 D5 D6
BAN CAR001 55 56 07pM AA
-- -- -- -- 07pM BB
-- -- -- -- 07pL CC
BAN CAR007 73 0 08pB CE
解决方案 »
- 切换不同的ORACLE_SID来通过操作系统来启动不同的实例
- oracle10g安装点击第一个“下一步”就报错,求救!
- 我在存储过程里拼起一个sql语句
- oracle如何填充嵌套表?
- oracle10g在连网和不连网的启动问题
- oracle磁盘空间满了,请大家帮助,感激万分,很急
- 急要触发器,大家帮帮忙
- 哪位有没有《Oracle 8i PL_SQL高级程序设计》英文版 给我一份。
- 高手: 安装 oracle 9.1 后,用 deamware 做一个 web 查询,用 DNS 连不上,是不是有什么特别的设置。
- oracle 32位如何从R1升级到R2,求补丁和具体步骤。谢谢!!
- 关于DBLINK的查询问题
- 游标的参数问题
case when a.a2 is null or b.b2 is null then '--' else a.a2 end d1,
case when a.a3 is null then '--' else to_char(a.a3) end d3,
case when b.b3 is null then '--' else a.a1 end d4,
c.c3 d5,c.c4 d6
from a,b,c
where c.c1=a.a1(+) and c.c2=a.a2(+)
and c.c1=b.b1(+) and c.c2=b.b2(+)
decode(a.a2,null,'--',a.a2) as d2,
decode(a.a3,null,'--',a.a3) as d3,
decode(b.b3,null,'--',b.b3) as d4,
c.c3 as d5,
c.c4 as d6
from a,b,c
where c.c1=a.a1(+)
and c.c1=b.b1(+)
and c.c2=a.a2(+)
and c.c2=b.b2(+)
from A a,B b,C c
where c.c2 = a.a2(+) and c.c2 = b.b2(+)
where c.c2 = a.a2(+)
里面的(+)是什么意思
謝謝 大家的支持! 期待大家的幫助!
case when row_number()over(partition by c.c1,c.c2,c.c3 order by rownum)>1 then '--' else c.c2 end d2,
case when row_number()over(partition by c.c1,c.c2,c.c3 order by rownum)>1 then '--' else to_char(a.a3) end d3,
case when row_number()over(partition by c.c1,c.c2,c.c3 order by rownum)>1 then '--' else to_char(b.b3) end d4,
c.c3 d5,c.c4 d6
from a,b,c
where c.c1=a.a1 and c.c2=a.a2
and c.c1=b.b1 and c.c2=b.b2
報錯:ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
報錯: ORA-00907: missing right parenthesis
union all select 'BAN' a1,'CAR007' a2,73 a3 from dual
union all select 'BAF' a1,'CAR105' a2,12 a3 from dual
union all select 'BAN' a1,'CAR911' a2,32 a3 from dual),
b as(select 'BAN' b1,'CAR001' b2,56 b3 from dual
union all select 'BAN' b1,'CAR911' b2,35 b3 from dual
union all select 'BAF' b1,'CAR105' b2,15 b3 from dual),
c as(select 'BAN' c1,'CAR001' c2,'07pm' c3,'AA' c4 from dual
union all select 'BAN' c1,'CAR001' c2,'07pm' c3,'BB' c4 from dual
union all select 'BAN' c1,'CAR001' c2,'07pl' c3,'CC' c4 from dual
union all select 'BAF' c1,'CAR105' c2,'01ph' c3,'DF' c4 from dual
union all select 'BAN' c1,'CAR007' c2,'08pb' c3,'CE' c4 from dual)
select case when row_number()over(partition by c.c1,c.c2 order by c4)>1 then '--' else c.c1 end d1,
case when row_number()over(partition by c.c1,c.c2 order by c4)>1 then '--' else c.c2 end d2,
case when row_number()over(partition by c.c1,c.c2 order by c4)>1 then '--' else to_char(nvl(a.a3,0)) end d3,
case when row_number()over(partition by c.c1,c.c2 order by c4)>1 then '--' else to_char(nvl(b.b3,0)) end d4,
c.c3 d5,c.c4 d6
from a,b,c
where c.c1=a.a1(+) and c.c2=a.a2(+)
and c.c1=b.b1(+) and c.c2=b.b2(+)
order by c.c1,d6BAF CAR105 12 15 01ph DF
BAN CAR001 55 56 07pm AA
-- -- -- -- 07pm BB
-- -- -- -- 07pl CC
BAN CAR007 73 0 08pb CE
比如partition by
請幫助
d1 d2 d3 d4 d5 d6 d7
926F1 -1 911-7357-001 960 0 07PM945405 7C4S0001
-- -- -- -- -- 07PM945405 7C4S0002
-- -- -- -- -- 07PM945405 7C4S0003
-- -- -- -- -- 07PM945405 7C4S0004
-- -- -- -- -- 07PM945405 7C4S0005
-- -- -- -- -- 07PM945405 7C4S0006
-- -- -- -- -- 09PV008461 7C4S0007
-- -- -- -- -- 09PV008461 7C4S0008
-- -- -- -- -- 09PV008461 7C4S0009
-- -- -- -- -- 09PV008461 7C4S0010
你可以将代码的case when row_number()over(partition by c.c1,c.c2 order by c4)>1 then '--' else c.c1 end ..都换成else后面的值,先取消'--'看下
926F1 -1 911-7357-001 960 0 07PM945405 7C4S0001
926F1 -- 911-7357-001 -- -- 07PM945405 7C4S0002
926F1 -- 911-7357-001 -- -- 07PM945405 7C4S0003
926F1 -- 911-7357-001 -- -- 07PM945405 7C4S0004
926F1 -- 911-7357-001 -- -- 07PM945405 7C4S0005
926F1 -- 911-7357-001 -- -- 07PM945405 7C4S0006
926F1 -- 912-V118H1-102 -- -- 08PV055884 7C4S0007
926F1 -- 912-V118H1-102 -- -- 08PV055884 7C4S0008
926F1 -- 912-V118H1-102 -- -- 08PV055884 7C4S0009
你显示了d1和d3,d2没有改
d1这里都重复啊,不知d2情况如何
还有你在14楼的回复中,d7为7C4S0009 对应记录的d6是09PV008461 ,到16楼怎么就变成08PV055884 了?
我的代码中的order by c.c1,d6改下
换成order by c.c1,c.c2,c.c4看看
-- -- -- -- 07pm BB
-- -- -- -- 07pl CC
BAN CAR007 73 0 08pb CE您這是我想要的結果
最后的order by c.c1,c.c2,c.c4要对上。其中c.c1和c.c2是parition by后面的字段名,c.c4是partition by 后面order by里的字段名
再试试
庫房 機種 ms_數量
926F1 911-7525H1-106 4180
926F1 912-7525H1-107 7700
926F1 912-V118H1-101 60
B
庫房 機種 mb_數量
926F1 911-7525H1-106 5750
926F1 911-7525H1-107 8300
926F1 911-7613H1-101 5380
C
機種 工單 箱號 庫房
911-7525H1-107 09PM029542 94JB3680 926F1
911-7525H1-107 09PM029542 94JB3694 926F1
911-7525H1-107 09PM029542 94JB3726 926F1
911-7525H1-106 09PM029544 94JB2662 926F1
911-7525H1-106 09PM029544 94JB2677 926F1
911-7525H1-106 09PM029544 94JB2680 926F1
結果
庫房 機種 ms_數量 mb_數量 工單 箱號 926F1 911-7525H1-106 4180 5750 09PM029544 94JB2662
--- --- --- --- 09PM029544 94JB2677
--- --- --- --- 09PM029544 94JB2680
926F1 911-7525H1-107 7700 8300 09PM029542 94JB3680
--- --- --- --- 09PM029542 94JB3694
--- --- --- --- 09PM029542 94JB3726