一道sql笔试题

表t_bind(id,reg_no,bind_no)
一个注册号码reg_no只能有3个绑定号码,怎么得到如下的结果?
reg_no,bind_no1,bind_no2,bind_no3
075582203401 13584568752 15384866523 18984865266
...... ...... ......

--drop table t_bind;

create table t_bind(
id int auto_increment primary key ,
reg_no varchar(20),
bind_no varchar(20)
);

insert into t_bind(reg_no,bind_no) values('075582203401','13584568752');
insert into t_bind(reg_no,bind_no) values('075582203401','18984865266');
insert into t_bind(reg_no,bind_no) values('075582203401','15384866523');
insert into t_bind(reg_no,bind_no) values('075582203402','15384866527');
insert into t_bind(reg_no,bind_no) values('075582203402','18984887525');


select m.reg_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;

执行结果:
+--------------+-------------+-------------+-------------+
| reg_no | bind_no1 | bind_no2 | bind_no3 |
+--------------+-------------+-------------+-------------+
| 075582203401 | 13584568752 | 15384866523 | 18984865266 |
| 075582203402 | 15384866527 | 18984887525 | NULL |
+--------------+-------------+-------------+-------------+
还有什么好的写法没?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值