VALUE ID
111 2
234 3
312 3
65 3
78 2
541 2
现在我需要把VALUE这一列根据ID的值分开,想得到的结果如下:
VALUE2 VALUE3
111 234
78 312
541 65
这个SQL语句怎么写啊?
8 个解决方案
#1
select a.value value2,b.value value2
from (select rownum rn,value from tb where id=2)a
left join (select rownum rn,value from tb where id=3)b
on a.rn=b.rn
from (select rownum rn,value from tb where id=2)a
left join (select rownum rn,value from tb where id=3)b
on a.rn=b.rn
#2
没看明白 ,帮顶
#3
别名不对
select a.value value2,b.value value3
from (select rownum rn,value from tb where id=2)a
left join (select rownum rn,value from tb where id=3)b
on a.rn=b.rn
select a.value value2,b.value value3
from (select rownum rn,value from tb where id=2)a
left join (select rownum rn,value from tb where id=3)b
on a.rn=b.rn
#4
又见行列转换贴,帮顶下!
#5
这样更有把握
select max(value2) value2,max(value3) value3
from
(select rownum rn,value2,null value3 from tb where id=2
union select rownum rn,null value2,value value3 from tb where id=3
)
group by rn
select max(value2) value2,max(value3) value3
from
(select rownum rn,value2,null value3 from tb where id=2
union select rownum rn,null value2,value value3 from tb where id=3
)
group by rn
#6
select a.value value1,b.value value2
from (select rownum rn,value,ID from tablea where id=2) a
left join
(select rownum rn,value,ID from tablea where id=3) b
on a.rn=b.rn;
用左连接left join 会出现一种情况,当id=3记录数大于id=2的记录数的时候,id=3的后面那几条记录就查询不出来了。
select max(value1) value1,max(value2) value2
from
(select rownum rn, value value1,null value2 from tablea where id=2
union select rownum rn,null value1,value value2 from tablea where id=3
)
group by rn;
还是这种方法好,不会漏掉!
from (select rownum rn,value,ID from tablea where id=2) a
left join
(select rownum rn,value,ID from tablea where id=3) b
on a.rn=b.rn;
用左连接left join 会出现一种情况,当id=3记录数大于id=2的记录数的时候,id=3的后面那几条记录就查询不出来了。
select max(value1) value1,max(value2) value2
from
(select rownum rn, value value1,null value2 from tablea where id=2
union select rownum rn,null value1,value value2 from tablea where id=3
)
group by rn;
还是这种方法好,不会漏掉!
#7
顶
#8
收藏学习
#1
select a.value value2,b.value value2
from (select rownum rn,value from tb where id=2)a
left join (select rownum rn,value from tb where id=3)b
on a.rn=b.rn
from (select rownum rn,value from tb where id=2)a
left join (select rownum rn,value from tb where id=3)b
on a.rn=b.rn
#2
没看明白 ,帮顶
#3
别名不对
select a.value value2,b.value value3
from (select rownum rn,value from tb where id=2)a
left join (select rownum rn,value from tb where id=3)b
on a.rn=b.rn
select a.value value2,b.value value3
from (select rownum rn,value from tb where id=2)a
left join (select rownum rn,value from tb where id=3)b
on a.rn=b.rn
#4
又见行列转换贴,帮顶下!
#5
这样更有把握
select max(value2) value2,max(value3) value3
from
(select rownum rn,value2,null value3 from tb where id=2
union select rownum rn,null value2,value value3 from tb where id=3
)
group by rn
select max(value2) value2,max(value3) value3
from
(select rownum rn,value2,null value3 from tb where id=2
union select rownum rn,null value2,value value3 from tb where id=3
)
group by rn
#6
select a.value value1,b.value value2
from (select rownum rn,value,ID from tablea where id=2) a
left join
(select rownum rn,value,ID from tablea where id=3) b
on a.rn=b.rn;
用左连接left join 会出现一种情况,当id=3记录数大于id=2的记录数的时候,id=3的后面那几条记录就查询不出来了。
select max(value1) value1,max(value2) value2
from
(select rownum rn, value value1,null value2 from tablea where id=2
union select rownum rn,null value1,value value2 from tablea where id=3
)
group by rn;
还是这种方法好,不会漏掉!
from (select rownum rn,value,ID from tablea where id=2) a
left join
(select rownum rn,value,ID from tablea where id=3) b
on a.rn=b.rn;
用左连接left join 会出现一种情况,当id=3记录数大于id=2的记录数的时候,id=3的后面那几条记录就查询不出来了。
select max(value1) value1,max(value2) value2
from
(select rownum rn, value value1,null value2 from tablea where id=2
union select rownum rn,null value1,value value2 from tablea where id=3
)
group by rn;
还是这种方法好,不会漏掉!
#7
顶
#8
收藏学习