自创一对多关联的SQL面试题(附答案)

in 博文 with 0 comment

背景

今天在工作中遇到一个业务需要两表关联,关联关系是一对多,要求要关联多方中时间最新的一条记录。SQL向来就很渣渣的我平时遇到这种需求直接就在代码中java来做了,但是今天的工时排得还比较轻松,时间也多,索性就跟这需求杠上了,今天就非要写SQL来实现。
功夫不负有心人,终于这个SQL还是把我整懵逼了(尴尬.jpg),僵持了大概一个多小时后,我想起了我们组的SQL大神----表哥,干脆就以这个案例做一道SQL面试题让表哥来做,哈哈。

SQL面试题

A表(TABLE_A)

IDNAME
1张三
2李四
3王五

B表(TABLE_B)

IDA_IDDESCDATE
111老哥啊2019-02-23 17:03:00
121老弟啊2019-02-23 17:10:00
132千年等一回2019-02-23 18:03:00
142一回又一回2019-02-23 18:10:00
153牡丹花下死2019-02-23 19:10:00
163做鬼也风流2019-02-23 19:03:00

*要求:

  1. A、B表关联,关联字段是A.ID = B.A_ID
  2. 查询出B表中DATE最大的记录,使得最终查询结果如下:
IDNAMEDESCDATE
1张三老弟啊2019-02-23 17:10:00
2李四一回又一回2019-02-23 18:10:00
3王五牡丹花下死2019-02-23 19:10:00

DDL语句

感兴趣的老铁可以执行DDL在数据库中跑下自己的SQL,对了仅限Mysql哦,Oracle有rownum也太简单了,哈哈,我是不是透露了什么?

CREATE TABLE `TABLE_A` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `TABLE_A` (`ID`, `NAME`) VALUES ('1', '张三');
INSERT INTO `TABLE_A` (`ID`, `NAME`) VALUES ('2', '李四');
INSERT INTO `TABLE_A` (`ID`, `NAME`) VALUES ('3', '王五');
CREATE TABLE `TABLE_B` (
  `ID` int(11) NOT NULL,
  `V_ID` int(11) NOT NULL,
  `DESC` varchar(255) DEFAULT NULL,
  `DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `TABLE_B` (`ID`, `A_ID`, `DESC`, `DATE`) VALUES ('11', '1', '老哥啊', '2019-02-23 17:03:00');
INSERT INTO `TABLE_B` (`ID`, `A_ID`, `DESC`, `DATE`) VALUES ('12', '1', '老哥啊', '2019-02-23 17:10:00');
INSERT INTO `TABLE_B` (`ID`, `A_ID`, `DESC`, `DATE`) VALUES ('13', '2', '千年等一回', '2019-02-23 18:03:00');
INSERT INTO `TABLE_B` (`ID`, `A_ID`, `DESC`, `DATE`) VALUES ('14', '2', '一回又一回', '2019-02-23 18:10:00');
INSERT INTO `TABLE_B` (`ID`, `A_ID`, `DESC`, `DATE`) VALUES ('15', '3', '牡丹花下死', '2019-02-23 19:10:00');
INSERT INTO `TABLE_B` (`ID`, `A_ID`, `DESC`, `DATE`) VALUES ('16', '3', '做鬼也风流', '2019-02-23 19:03:00');

参考答案

SELECT 
t1.ID,
t1.NAME,
t2.ID,
t2.DESC,
t2.DATE
FROM TABLE_A t1
JOIN
(SELECT
e.*,
CASE WHEN e.A_ID != @fhId THEN @orderNum:=1 ELSE @orderNum:=@orderNum+1 END,
CASE WHEN @orderNum = 1 THEN @fhId:=e.V_ID ELSE @fhId END,
@orderNum AS 'orderNum'
FROM
TABLE_B e JOIN (SELECT @orderNum:=0, @fhId:='-1') T2 
ORDER BY
e.A_ID,
e.DATE DESC,
e.ID DESC) t2
ON t1.ID = t2.A_ID
WHERE t2.orderNum = 1

总结

当表哥三下五除二把这个SQL写给我的时候我懵逼了,表哥不亏是混迹江湖七八年的老SQLer,让我人生第一次见识到SQL还有如此风骚的操作,不得不让人佩服。
回到主题,从SQL中不难看出思路,关联B表之间先把B表按DATE降序的要求排序,然后打上排名顺序123...然后AB表关联后筛选B表中顺序为1的即可。

试想如果把题目改一改,B表长这样:

IDA_IDDESCDATEORDER
111老哥啊2019-02-23 17:03:002
121老弟啊2019-02-23 17:10:001
132千年等一回2019-02-23 18:03:002
142一回又一回2019-02-23 18:10:001
153牡丹花下死2019-02-23 19:10:001
163做鬼也风流2019-02-23 19:03:002

然后要求改为:AB表关联并筛选出B表中ORDER=1的数据,这道题是不是就是送分题了呢?所以为啥说用Oracle来解决这题就简单了因为自带了rownum属性字段,做起来就跟改后的题没区别了,用Mysql难就难在需要自己去写一个rownum。

好了,装逼结束。反正应该也不会有人看,哈哈!