一道 SQL 面试题

[Aha1]

• 编写一个 SQL 查询，与上一次成绩相比成绩更好的考试日期
  ID Date Score

[markgor]

SELECT Date FROM table ORDER BY Score DESC LIMIT 1

[xx19941215]

select `s2`.`date` from `score` `s2` left join `score` `s1` on `s2`.`score` > `s1`.`score` where `s2`.`id` = `s1`.`id` + 1;

[xx19941215]

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` timestamp NULL DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOCK TABLES `score` WRITE;
/*!40000 ALTER TABLE `score` DISABLE KEYS */;

INSERT INTO `score` (`id`, `date`, `score`)
VALUES
(1,'2018-10-10 00:00:00',90),
(2,'2018-10-11 00:00:00',100),
(3,'2018-10-16 00:00:00',89),
(4,'2018-10-17 00:00:00',91),
(5,'2018-10-18 00:00:00',92);

/*!40000 ALTER TABLE `score` ENABLE KEYS */;
UNLOCK TABLES;

表结构

[x66]

2 楼的 SQL 如果 id 不连续就 GG 了。

SELECT t1.date FROM test t1
WHERE t1.scope > ( SELECT t2.scope from test t2 where t2.date = ( SELECT max( t3.date ) FROM test t3 WHERE t3.date < t1.date ))

[x66]

@x66 #4 scope =============> score 建表的时候写错了

[lasuar]

先求出上一次考试日期
set @last_date = SELECT MIN(Date) FROM tb ORDER BY Date DESC LIMIT 2 （我理解上一次考试之后还有一次考试哈）
再求出上一次考试成绩
set @last_score = SELECT score FROM tb WHERE Date =last_date
最后求出比[上一次考试的成绩]还要好的[那一 /几次考试的日期]
SELECT Date FROM tb WHERE score>last_score
结果可能有多个。写成一条 SQL 即可

[carlclone]

有个思路, 两个相同的表按 date 排序,有一张表去掉最新的一条,用他们的 order 进行关联后比较大小,sql 应该能实现吧?

ID DATE Score Order ID Date Score Order
3 0923 95 1 2 0918 50 1
2 0918 50 2 1 0917 90 2
1 0917 90 3

[carlclone]

....发出来格式变了

[Alexhohom]

select x.Date from table as x left join (select top 1 * from table order by dtTime desc)x1 on 1=1 where x.Score>x1.Score

[Alexhohom]

@Alexhohom #9 dtTime 是 Date...用的自己的库测试的

[liprais]

这个题只要把考试的顺序搞出来再来个自关联就行了
mysql 没有窗口函数可能麻烦一点

[sutra]

create table t(id integer, date timestamp with time zone, score float);
insert into t(id, date, score);
INSERT INTO t (id, date, score)
VALUES
(1,'2018-10-10 00:00:00',90),
(2,'2018-10-11 00:00:00',100),
(3,'2018-10-16 00:00:00',89),
(4,'2018-10-17 00:00:00',91),
(5,'2018-10-18 00:00:00',92);

select * from t;

id | date | score
----+------------------------+-------
1 | 2018-10-10 00:00:00+08 | 90
2 | 2018-10-11 00:00:00+08 | 100
3 | 2018-10-16 00:00:00+08 | 89
4 | 2018-10-17 00:00:00+08 | 91
5 | 2018-10-18 00:00:00+08 | 92
(5 rows)

select t.id, t.date, t.score
from (
select full_cmp.id1 from (
select t0.id id0, t1.id id1
from t t0, t t1
where
t1.date > t0.date
and t1.score > t0.score
order by t1.date
) full_cmp
group by full_cmp.id1
) cmp, t
where cmp.id1 = t.id
order by t.date;

[sutra]

上面一个回复漏了结果：

id | date | score
----+------------------------+-------
2 | 2018-10-11 00:00:00+08 | 100
4 | 2018-10-17 00:00:00+08 | 91
5 | 2018-10-18 00:00:00+08 | 92
(3 rows)

[sutra]

再补充下，上面这个是在 PostgreSQL 下测试的，没注意看节点是 MySQL ……

[reus]

换了有窗口函数的，一个 lag 拍上去就行
select date from (
select score - lag(score, 1) over (order by date asc) as diff, date
from scores
) t0
where diff > 0

PostgreSQL 和 MySQL 8 都支持的。

[liprais]

@sutra pg 为啥不用 window function

[sutra]

@reus @liprais 👍

[zhuawadao]

SELECT t1.date from
(
select (@rowNO := @rowNo+1) AS rowno,a.date ,a.score
from (SELECT date,score FROM score ORDER BY date desc) a,(select @rowNO :=0) b) t1,
(
select (@rowNO := @rowNo+1) AS rowno,a.date ,a.score
from (SELECT date,score FROM score ORDER BY date desc) a,(select @rowNO :=0) b) t2
where t1.rowno=t2.rowno+4 and t1.score>t2.score

[Gathaly]

id date score
1 2018-10-10 00:00:00 90
2 2018-10-11 00:00:00 100
3 2018-10-16 00:00:00 89
4 2018-10-17 00:00:00 91
5 2018-10-18 00:00:00 92

先做自连接，找出右边比左边分数高，且日期晚的项，然后再 group by 右表去除重复行

SELECT

t2.`id`,

MAX(t2.`date`)

FROM score t1 ,score t2

WHERE

t2.`score` > t1.`score` AND

t2.`date` > t1.`date`

GROUP BY

t2.`id`, t2.`date`