现在我有表T1
A B C
301 a 10
302 b 20
303 c 30
表T2
D E
301 Array
301 Cell
302 Array
303 Cell
我想得到的结果是:
A B count
301 a 2
302 b Array
303 c Cell
请高手们快点进来帮一下
A B C
301 a 10
302 b 20
303 c 30
表T2
D E
301 Array
301 Cell
302 Array
303 Cell
我想得到的结果是:
A B count
301 a 2
302 b Array
303 c Cell
请高手们快点进来帮一下
解决方案 »
- 如何oracle 在 apex 做上传文件的页面?
- oracle中表字段为null时页面就有可能报错
- 关于SQL合并字段的问题。。。急!!!
- oracle9.2.0.7在哪里下载啊
- 请教:如何解决困扰我多时的ORACLE的日期问题?
- 如何将一台机器上的oracle数据库完全移植到另外一台机器上?
- 谁能把MS SQL SERVER 2000与ORACLE的基础概念做一下比较说明。
- 在线的兄弟来帮帮呀!
- 能不能在一个SQL的删除语句中同时对两个表进行删除?
- oracle高手请进关于两个数据中表数据的传递问题 up有分(在线急等)
- 用存储过程在temps表里插入五十条数据简单问题
- 用存储过程输出数据库中的所有SCHEMA出错
A B C
301 a 10
302 b 20
303 c 30
表T2
D E
301 Array
301 Cell
302 Array
303 Cell
我想得到的结果是:
A B count
301 a 2
302 b Array
303 c Cell
请高手们快点进来帮一下
302 b Array --如果是count的话,那你这个Array、Cell结果怎么来的?
303 c Cell
我想要的结果是count等于2就显示2,不等于2就显示表上的值
t1 as(
select 301 as a, 'a' as b, 10 as c from dual
union all
select 302 as a, 'b' as b, 20 as c from dual
union all
select 303 as a, 'c' as b, 30 as c from dual
),
t2 as(
select 301 as d, 'Array' as e from dual
union all
select 301 as d, 'Cell' as e from dual
union all
select 302 as d, 'Array' as e from dual
union all
select 303 as d, 'Cell' as e from dual
),
t3 as(
select t1.a, wmsys.wm_concat(t2.e) e
from t1 join t2 on t1.a=t2.d
group by t1.a)
select t3.a,
decode(instr(t3.e,',',1,1),0,t3.e,to_char(length(t3.e)-length(replace(t3.e,',',''))+1)) as e
from t3;
2 t1 as(
3 select 301 as a, 'a' as b, 10 as c from dual
4 union all
5 select 302 as a, 'b' as b, 20 as c from dual
6 union all
7 select 303 as a, 'c' as b, 30 as c from dual
8 ),
9 t2 as(
10 select 301 as d, 'Array' as e from dual
11 union all
12 select 301 as d, 'Cell' as e from dual
13 union all
14 select 302 as d, 'Array' as e from dual
15 union all
16 select 303 as d, 'Cell' as e from dual
17 ),
18 t3 as(
19 select t1.a, wmsys.wm_concat(t2.e) e
20 from t1 join t2 on t1.a=t2.d
21 group by t1.a)
22 select t3.a,
23 decode(instr(t3.e,',',1,1),0,t3.e,to_char(length(t3.e)-length(replace(t3.e,',',''))+1)) as e
24 from t3; A E
---------- -------------------------------------------------------
301 2
302 Array
303 Cell
t1 as(
select 301 as a, 'a' as b, 10 as c from dual
union all
select 302 as a, 'b' as b, 20 as c from dual
union all
select 303 as a, 'c' as b, 30 as c from dual
),
t2 as(
select 301 as d, 'Array' as e from dual
union all
select 301 as d, 'Cell' as e from dual
union all
select 302 as d, 'Array' as e from dual
union all
select 303 as d, 'Cell' as e from dual
),
t3 as(
select t1.a, t1.b, wmsys.wm_concat(t2.e) e
from t1 join t2 on t1.a=t2.d
group by t1.a, t1.b)
select t3.a,
t3.b,
decode(instr(t3.e,',',1,1),0,t3.e,to_char(length(t3.e)-length(replace(t3.e,',',''))+1)) as e
from t3;
select t1.a, t1.b, to_char(count(*)) c
from t1, t2
where t1.a = t2.d
group by t1.a, t1.b
having count(*) > 1
union all
select t1.a, t1.b, t2.e c
from t1, t2
where t1.a = t2.d
and t1.a in (select t1.a
from t1, t2
where t1.a = t2.d
group by t1.a, t1.b
having count(*) = 1)
with t as(
select 301 A,'a' B,10 C from dual union all
select 302,'b',20 from dual union all
select 303,'c',30 from dual),
t2 as(
select 301 D,'Array' E from dual union all
select 301,'Cell' from dual union all
select 302,'Array' from dual union all
select 303,'Cell' from dual)
select distinct A,B,(case when cnt=2 then to_char(cnt) else E end)
from (select A,B,count(*) cnt
from t,t2
where t.A=t2.D
group by A,B)tb ,t2
where tb.A=t2.D
A B (CASEWHENCNT=2THENTO_CHAR(CNT)ELSEEEND)
---------- - ----------------------------------------
302 b Array
303 c Cell
301 a 2