自创一对多关联的SQL面试题（附答案）

数据库

背景

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

SQL面试题

A表（TABLE_A）

B表（TABLE_B）

*要求：

1. A、B表关联，关联字段是A.ID = B.A_ID
2. 查询出B表中DATE最大的记录，使得最终查询结果如下：

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,
  `A_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.A_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表长这样：

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

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