现在我有表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
请高手们快点进来帮一下
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