这些年面试遇到的mysql题

<p><br></p><p>1. 有一个航班表，有班次、起飞地址、起飞时间、着陆地址、着陆时间，请写出查询‘深圳’到‘南京’且需要转机的航班班次的sql语句。</p><p>示例表：</p><pre> create table plane(<br> airline_no  char(10) not null,<br> start_area varchar(20) not null,<br> start_time datetime not null,<br> land_area varchar(20) not null,<br> end_time datetime not null,<br> primary key(airline_no)<br>)<br>engine=innodb<br>collate=utf8_general_ci<br>auto_increment = 1<br></pre><p><br></p><p>方法一：</p><pre><span style='display: inline !important; float: none; background-color: rgb(245, 245, 245); color: rgb(51, 51, 51); font-family: Menlo,Monaco,Consolas,"Courier New",monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: 1.4285; orphans: 2; overflow-wrap: break-word; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: pre; word-break: break-all; word-spacing: 0px;'>select p1.* from plane p1 where p1.land_area in (</span><span style='display: inline !important; float: none; background-color: rgb(245, 245, 245); color: rgb(51, 51, 51); font-family: Menlo,Monaco,Consolas,"Courier New",monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: 1.4285; orphans: 2; overflow-wrap: break-word; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: pre; word-break: break-all; word-spacing: 0px;'>select start_area from plane p2 </span>    <span style='display: inline !important; float: none; background-color: rgb(245, 245, 245); color: rgb(51, 51, 51); font-family: Menlo,Monaco,Consolas,"Courier New",monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: 1.4285; orphans: 2; overflow-wrap: break-word; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: pre; word-break: break-all; word-spacing: 0px;'>where p2.land_area='南京' and p1.land_time &lt; p2.start_time ) </span><b></b><i></i><u></u><strike></strike><br></pre><p>方法二：</p><pre>select * from plane p1 left join     (select airline_no, start_area, start_time, land_area, land_time from plane) p2 on p1.land_area = p2.start_area and p1.land_time &lt; p2.start_time where p1.start_area = '深圳' and p2.land_area = '南京'<br></pre><div>两种方法的性能待评估。</div><div><br></div><div>2. 有张用户借贷申请表，还有一张借贷交易表，请查出某借款用户id为1的10位最大债主。</div><div>关于表的明细如下：</div><div>借贷申请表：</div><pre> create table loan_apply(<br> apply_id int(11)  not null auto_increment comment '申请Id',<br> user_id int(11) not null comment '申请人id',<br> load_amount int(11) not null default 0 comment '申请贷款金额(元)',<br> create_at datetime not null default current_timestamp,<br> primary key(apply_id)<br>)<br>engine=innodb<br>collate=utf8_general_ci<br>auto_increment = 1<br></pre><div>放贷表：</div><pre> create table loan_lend(<br> lend_id  int(11)  not null auto_increment comment '可放款ID',<br> lend_user_id int(11) not null comment '放款人id',<br> lend_amount int(11) not null default 0 comment '放款金额',<br> apply_id int(11) null comment '借款申请Id',<br> create_at datetime not null default current_timestamp,<br> primary key(lend_id)<br>)<br>engine=innodb<br>collate=utf8_general_ci<br>auto_increment = 1<br></pre><div><br></div><div>查询sql语句：</div><pre><!--StartFragment--><div>select sum(l.lend_amount) as lend_money, l.lend_user_id from loan_lend l <br>left join loan_apply a on l.apply_id = a.apply_id <br>where a.user_id = 1 <br>group by l.lend_user_id<br>order by lend_money desc <br>limit 10;<br></div></pre><blockquote>关键词顺序：where,group by, having, order by,limit</blockquote><div><br></div><div><br></div>