大家好,如果我有一个表a:
no, attribute, value
1 inner x1
1 outer y1
2 outer y2
3 inner x3
4 inner x4
4 outer y4
我想用sql实现表b:
no, value
1 x1
2 y2
3 x3
4 x4,
即,如果attributable 的属性有inner,outer我只取 inner 的值,如果没有inner,那么就取outer 得值。
我觉得应该很容易实现,但是我的程序执行的结果和实际却不相符,希望有人能告诉我,该怎么实现。谢谢!!
no, attribute, value
1 inner x1
1 outer y1
2 outer y2
3 inner x3
4 inner x4
4 outer y4
我想用sql实现表b:
no, value
1 x1
2 y2
3 x3
4 x4,
即,如果attributable 的属性有inner,outer我只取 inner 的值,如果没有inner,那么就取outer 得值。
我觉得应该很容易实现,但是我的程序执行的结果和实际却不相符,希望有人能告诉我,该怎么实现。谢谢!!
from
(
select no,max(decode(attribute,'inner',value ,null))inner_value,
max(decode(attribute,'outer',value ,null))outer_value
from a
group by no
)
2 union
3 select 1 no,'outer' attribute,'y1' value from dual
4 union
5 select 2 no,'outer' attribute,'y2' value from dual
6 union
7 select 3 no,'inner' attribute,'x3' value from dual
8 union
9 select 4 no,'inner' attribute,'x4' value from dual
10 union
11 select 4 no,'outer' attribute,'y4' value from dual
12 )
13 select no,value1 from a
14 where attribute='inner'
15 union
16 select a.no,a.value1
17 from a,(select no from a group by no having count(no)=1) b
18 where a.no=b.no
19 /
NO VALUE1
---------- ------
1 x1
2 y2
3 x3
4 x4
from (select no,attribute,value, row_number() over (partition by no,attribute order by no,attribute) row_no from a)
where row_no=1;
from (select no,attribute,value, row_number() over (partition by no order by no,attribute) row_no from a)
where row_no=1;