职业经验 # 每日一道面试题 # 在一个数据库表中,有不同天的多条记录,用 sql 找出间隔最久没有数据的间隔

JinZhu for 求职面试圈 · 2018年03月13日 · 最后由 by_link 回复于 2018年03月15日 · 1901 次阅读

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
最佳回复
假设数据是这样的,日期各不相同,且已经按时间升序排序

错开一行,生成新的数据表,并计算时间间隔

再按时间差降序排序,取第一个即可

SELECT NewTable.DataTime, NewTable.NewDate, DATEDIFF(NewTable.DataTime, NewTable.NewDate) AS Diff
FROM (
SELECT DataTime
, (
SELECT DataTime
FROM TestTest Alias
WHERE TestTest.DataTime > Alias.DataTime
ORDER BY DataTime DESC
LIMIT 1
) AS NewDate
FROM TestTest
) NewTable
ORDER BY Diff DESC
LIMIT 1

题目可以理解为比较同一个表中每行的日期列,然后选择一个值最大的,说白了就是上下相邻的两个数据进行比较,然后选出差值最大一个。
答案为:select t1.id, t1.create_time, t2.id, t2.create_time, timediff(t2.create_time, t1.create_time) as diff from timestamps t1 inner join timestamps t2 on t1.id +1 = t2.id order by diff desc limit 1;

#t1表为原始表,t2表可以理解原始表的复制表,
下面以mysql为例,使用了mysql自连接,即同一个表自己和自己进行连表查询(一般遇到最多的是不同的两个表进行连表查询,如inner join, left join, right join 等等)
数据库表类似:

下面为SQL语句:
select t1.id, t1.create_time, t2.id, t2.create_time, timediff(t2.create_time,t1.create_time) as diff from timestamps t1 inner join timestamps t2 on t1.id +1 = t2.id;

#关键点1: on t1.id+1 = t2.id,意思是连表查询的条件为原始表的id + 1与复制表的id相同,这样做的目的就是为了把原始表的第一行的数据与复制表的下一行的数据生成于连表查询的结果表同一行中;(说白了就是同一个表的数据错行显示在结果表上)
关键点2: timediff(t2.create_time, t1.create_time) as diff, 这样做是对t2的列对t1表的列取差值,并取别名(为后面的排序做准备)
查询结果如下:

排序使用order by diff;找出间隔最大的那一个,即使用limit 1;最后查询结果如下:

当然如果你只想显示最大的值
select timediff(t2.create_time, t1.create_time) as diff from timestamps t1 inner join timestamps t2 on t1.id +1 = t2.id order by diff desc limit 1;
结果如下:

希望能面试的上。😂

情况1,找到两条连续的数据中,时间间隔最大的

select max(
TIMESTAMPDIFF(
SECOND,
ii.cTime,
(
select cTime
from issues
where id=(select min(id) from issues where id > ii.id)
)
)
) as maxDateRange
from issues as ii

情况2,找到所有数据中最大的时间差?

select TIMESTAMPDIFF(SECOND, MIN(cTime), MAX(cTime)) as maxDataRange from issues
共收到 15 条回复 时间 点赞

不错的问题

使用Max和DATEDIFF函数就行了吧,感觉DATEDIFF这个函数用的少

数据测试工程师的面试题

假设数据是这样的,日期各不相同,且已经按时间升序排序

错开一行,生成新的数据表,并计算时间间隔

再按时间差降序排序,取第一个即可

SELECT NewTable.DataTime, NewTable.NewDate, DATEDIFF(NewTable.DataTime, NewTable.NewDate) AS Diff
FROM (
SELECT DataTime
, (
SELECT DataTime
FROM TestTest Alias
WHERE TestTest.DataTime > Alias.DataTime
ORDER BY DataTime DESC
LIMIT 1
) AS NewDate
FROM TestTest
) NewTable
ORDER BY Diff DESC
LIMIT 1

我选择用cursor 外加一个临时变量,循环比对,这种写法在oracle时代用的不要太多哦

arrow 回复

请教下,这个错开的sql看不太懂,就解释……

Lihuazhang 回复

嗯,应该把SQL分开会易懂一些

SELECT DataTime,
(
SELECT DataTime
FROM TestTest Alias
WHERE TestTest.DataTime > Alias.DataTime
ORDER BY DataTime DESC
LIMIT 1
) AS NewDate
FROM TestTest

这块sql执行出来就是第2张图的结果,括号里面的sql对应第二列数据(NewDate)。
因为日期已经排好序了,所以取比DataTime小的日期里面最大的日期,就是NewDate。
不知道这样说明白了没。。

arrow 回复
SELECT DataTime
FROM TestTest Alias
WHERE TestTest.DataTime > Alias.DataTime
ORDER BY DataTime DESC
LIMIT 1

这个里面是什么意思?是mysql吗?

Lihuazhang 回复

嗯,mysql

话说“用 sql 找出间隔最久没有数据的间隔” 也不加个标点,我都没看懂问题

仅楼主可见

我明白了,关键在:

SELECT DataTime,
(
SELECT DataTime
FROM TestTest Alias
WHERE TestTest.DataTime > Alias.DataTime
ORDER BY DataTime DESC
LIMIT 1
) AS NewDate
FROM TestTest

里面


SELECT DataTime
FROM TestTest Alias
WHERE TestTest.DataTime > Alias.DataTime
ORDER BY DataTime DESC
LIMIT 1

这个子查询不能单独存在,必须和外面的一起。

题目可以理解为比较同一个表中每行的日期列,然后选择一个值最大的,说白了就是上下相邻的两个数据进行比较,然后选出差值最大一个。
答案为:select t1.id, t1.create_time, t2.id, t2.create_time, timediff(t2.create_time, t1.create_time) as diff from timestamps t1 inner join timestamps t2 on t1.id +1 = t2.id order by diff desc limit 1;

#t1表为原始表,t2表可以理解原始表的复制表,
下面以mysql为例,使用了mysql自连接,即同一个表自己和自己进行连表查询(一般遇到最多的是不同的两个表进行连表查询,如inner join, left join, right join 等等)
数据库表类似:

下面为SQL语句:
select t1.id, t1.create_time, t2.id, t2.create_time, timediff(t2.create_time,t1.create_time) as diff from timestamps t1 inner join timestamps t2 on t1.id +1 = t2.id;

#关键点1: on t1.id+1 = t2.id,意思是连表查询的条件为原始表的id + 1与复制表的id相同,这样做的目的就是为了把原始表的第一行的数据与复制表的下一行的数据生成于连表查询的结果表同一行中;(说白了就是同一个表的数据错行显示在结果表上)
关键点2: timediff(t2.create_time, t1.create_time) as diff, 这样做是对t2的列对t1表的列取差值,并取别名(为后面的排序做准备)
查询结果如下:

排序使用order by diff;找出间隔最大的那一个,即使用limit 1;最后查询结果如下:

当然如果你只想显示最大的值
select timediff(t2.create_time, t1.create_time) as diff from timestamps t1 inner join timestamps t2 on t1.id +1 = t2.id order by diff desc limit 1;
结果如下:

希望能面试的上。😂

情况1,找到两条连续的数据中,时间间隔最大的

select max(
TIMESTAMPDIFF(
SECOND,
ii.cTime,
(
select cTime
from issues
where id=(select min(id) from issues where id > ii.id)
)
)
) as maxDateRange
from issues as ii

情况2,找到所有数据中最大的时间差?

select TIMESTAMPDIFF(SECOND, MIN(cTime), MAX(cTime)) as maxDataRange from issues

mysql:使用时间和行号存储为一个结果集,存储两个(用来比较的),结果集中按时间排序,保证结果的排序规则一致;然后使用第二个结果集的下一行与第一个结果集的当前行相减,然后取最大的。
select max(to_days(t2.test_time) - to_days(t1.test_time)) from
(select test_time,(@row_no:=@row_no+1) as row_no from testreport,(select (@row_no:=0)) b order by test_time) t1
join (select test_time,(@row_no1:=@row_no1+1) as row_no from testreport,(select (@row_no1:=0)) b order by test_time) t2
on t1.row_no = t2.row_no-1

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册