表t_bind(id,reg_no,bind_no)
一个注册号码reg_no只能有3个绑定号码,请教一下:怎么得到这样的结果啊,谢谢
reg_no,bind_no1,bind_no2,bind_no3
02098339935,13366666666,15366666666,18966666666
一个注册号码reg_no只能有3个绑定号码,请教一下:怎么得到这样的结果啊,谢谢
reg_no,bind_no1,bind_no2,bind_no3
02098339935,13366666666,15366666666,18966666666
02098339935,15366666666
02098339935,18966666666
先记录,上面的数据,然后要调用的时候,在做行转行!
ORALCE 10G用本身的函数wm_concat
oracle 9i 用Sys_Connect_By_Path
max(case when rn=2 then bind_no) bind_no2,
max(case when rn=3 then bind_no) bind_no3 from(
select reg_no,bind_no,row_number()over(partition by reg_no order by bind_no)rn
from t_bind)
group by reg_no
我现在mysql下做测试的
id number(4),
reg_no varchar2(20),
bind_no varchar2(20)
);
insert into t_bind values(1,'02098339935','13366666666');
insert into t_bind values(2,'02098339935','15366666666');
insert into t_bind values(3,'02098339935','18966666666');
insert into t_bind values(4,'02098339931','13366666666');
insert into t_bind values(5,'02098339931','15366666666');
insert into t_bind values(6,'02098339931','18966666666');select t1.reg_no,t1.BIND_NO,t2.bind_no,t3.bind_no from
(select rownum as num,reg_no,bind_no from t_bind where reg_no='02098339931') t1
left join (select rownum as num,reg_no,bind_no from t_bind where reg_no='02098339931') t2 on t1.reg_no=t2.reg_no and t2.num=2
left join (select rownum as num,reg_no,bind_no from t_bind where reg_no='02098339931') t3 on t1.reg_no=t3.reg_no and t3.num=3
where t1.num=1 ;
查询结果:
1 02098339931 13366666666 15366666666 18966666666
以上少了一个 endselect reg_no,max(case when rn=1 then bind_no end) bind_no1,
max(case when rn=2 then bind_no end) bind_no2,
max(case when rn=3 then bind_no end) bind_no3 from(
select reg_no,bind_no,row_number()over(partition by reg_no order by bind_no)rn
from t_bind)
group by reg_no
我现在只能mysql下做试验,能不能不用某种特定数据库的东西.
不知道哪些通用...
麻烦在bind_no的序号上
不能用分析函数..
看看别人有什么好办法
(select min(bind_no) from t_bind where reg_no=m.reg_no) bind_no1,
(select min(bind_no) from t_bind where reg_no=m.reg_no and bind_no>(select min(bind_no) from t_bind where reg_no=m.reg_no)) bind_no2,
(select max(bind_no) from t_bind where reg_no=m.reg_no and (select count(*) from t_bind where reg_no=m.reg_no)>2) bind_no3
from t_bind as m group by m.reg_no;