面试题目，有点变态，朋友们，帮忙写个sql语句

yangfan_25845220 2010-10-27 11:22:44
题目：查询2005年全年没有下雨的记录，要求用一条语句完成

返回结果：全年的没有下雨的时间区间

已知条件：
数据表已经记录了下雨的记录，每一条是以：下雨的开始时间、下雨的结束时间，来进行记录
数据表结构：StartTime EndTime
例：
2005-1-20 0:00:00 2005-1-21 0:00:00
2005-1-29 0:00:00 2005-1-30 0:00:00
2005-2-15 0:00:00 2005-2-19 0:00:00
2005-2-21 0:00:00 2005-2-12 0:00:00


谢谢啦！！！
...全文
327 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
Sweet-Tang 2010-10-27
  • 打赏
  • 举报
 回复 


declare @table Table(StartDate datetime,EndDate datetime)

insert @table values

('2005-1-20 0:00:00','2005-1-21 0:00:00'),  

('2005-1-29 0:00:00','2005-1-30 0:00:00'), 

('2005-2-15 0:00:00','2005-2-19 0:00:00'), 

('2005-2-21 0:00:00','2005-2-24 0:00:00')



select DATEADD(DAY,1,T1.EndDate),DATEADD(DAY,-1,MIN(T2.StartDate)) from @table T1

left join @table T2 on DATEADD(DAY,1,T1.EndDate)<  T2.StartDate 

 group by T1.EndDate

 结果

2005-01-22 00:00:00.000	2005-01-28 00:00:00.000

2005-01-31 00:00:00.000	2005-02-14 00:00:00.000

2005-02-20 00:00:00.000	2005-02-20 00:00:00.000

2005-02-25 00:00:00.000	NULL
zhangyangziwo 2010-10-27
  • 打赏
  • 举报
 回复
create table a (StartTime datetime,EndTime datetime)


insert into a
select '2005-1-20 0:00:00', '2005-1-21 0:00:00 '
union
select '2005-1-29 0:00:00', '2005-1-30 0:00:00'
union
select '2005-2-15 0:00:00', '2005-2-19 0:00:00'
union
select '2005-2-21 0:00:00', '2005-2-22 0:00:00'
union
select null,'2005-01-01 0:00:00'
union
select '2005-12-31 23:59:59',null

select a.endtime,(select top 1 b.starttime from a as b where b.starttime>a.endtime order by starttime)
from a
where endtime is not null
order by a.endtime
SQLCenter 2010-10-27
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 coleling 的回复:]

SQL code

--借用#8的建表和演示数据
select dateadd(d,1,a.endtime) as starttime, dateadd(d,-1,b.starttime) as endtime from
(
select row_number() over(order by endtime) as id, endtime
from (select '200……
[/Quote]

不错的思路

不过最后还是要 where dateadd(d,1,a.endtime)<=dateadd(d,-1,b.starttime)

否则跨年下雨会有问题。
SQL77 2010-10-27
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 sql77 的回复:]
引用 7 楼 yangfan_25845220 的回复:
我做出来了，有点笨，但是还是完成了，大家看看哪里要修改下
WITH TempTable AS
(
SELECT *,
ROW_NUMBER() OVER (order by StartTime)as RowNumber
FROM t_2
)
select dateadd(day,1,ta.EndTime) as StartT……
[/Quote]
不过这样的应该不会有交叉,多虑了
SQL77 2010-10-27
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 yangfan_25845220 的回复:]
我做出来了，有点笨，但是还是完成了，大家看看哪里要修改下
WITH TempTable AS
(
SELECT *,
ROW_NUMBER() OVER (order by StartTime)as RowNumber
FROM t_2
)
select dateadd(day,1,ta.EndTime) as StartTime, dateadd(day,-1,tb.Start……
[/Quote]
如果有交叉的情况那就不对了,
coleling 2010-10-27
  • 打赏
  • 举报
 回复 


--借用#8的建表和演示数据

select dateadd(d,1,a.endtime) as starttime, dateadd(d,-1,b.starttime) as endtime from 

(

	select row_number() over(order by endtime) as id, endtime 

	from (select '2004-12-31' as endtime union all select endtime from #) a

) a join (

	select row_number() over(order by starttime) as id,starttime 

	from (select '2006-1-1' as starttime union all select starttime from #) a

) b on b.id = a.id



/*

starttime               endtime

----------------------- -----------------------

2005-01-01 00:00:00.000 2005-01-19 00:00:00.000

2005-01-22 00:00:00.000 2005-01-28 00:00:00.000

2005-01-31 00:00:00.000 2005-02-14 00:00:00.000

2005-02-20 00:00:00.000 2005-02-20 00:00:00.000

2005-02-23 00:00:00.000 2005-12-31 00:00:00.000



(5 行受影响)

*/
yangfan_25845220 2010-10-27
  • 打赏
  • 举报
 回复
是这样的，呵呵，强人哈

面试的时候没有写出来，被羞辱了，哎！！！
SQLCenter 2010-10-27
  • 打赏
  • 举报
 回复
把日期错位不就可以了吗
SQLCenter 2010-10-27
  • 打赏
  • 举报
 回复
cte 应该算1条语句吧，不是就把cte写成子查询：

--> 测试数据：#

if object_id('tempdb.dbo.#') is not null drop table #

create table #(StartTime datetime, EndTime datetime)

insert into #

select '2005-1-20 0:00:00', '2005-1-21 0:00:00' union all

select '2005-1-29 0:00:00', '2005-1-30 0:00:00' union all

select '2005-2-15 0:00:00', '2005-2-19 0:00:00' union all

select '2005-2-21 0:00:00', '2005-2-22 0:00:00'



;with cte as

(

	select id=row_number()over(order by StartTime), *  from # where 2005 between year(StartTime) and year(EndTime)

)

select isnull(a.EndTime+1,'2005')a, isnull(b.StartTime-1, '20051231')b from cte a full join cte b on a.id=b.id-1

where isnull(a.EndTime+1,'2005')<=isnull(b.StartTime-1, '20051231')

/*

a                       b

----------------------- -----------------------

2005-01-01 00:00:00.000 2005-01-19 00:00:00.000

2005-01-22 00:00:00.000 2005-01-28 00:00:00.000

2005-01-31 00:00:00.000 2005-02-14 00:00:00.000

2005-02-20 00:00:00.000 2005-02-20 00:00:00.000

2005-02-23 00:00:00.000 2005-12-31 00:00:00.000

*/
yangfan_25845220 2010-10-27
  • 打赏
  • 举报
 回复
我做出来了，有点笨，但是还是完成了，大家看看哪里要修改下
WITH TempTable AS
(
SELECT *,
ROW_NUMBER() OVER (order by StartTime)as RowNumber
FROM t_2
)
select dateadd(day,1,ta.EndTime) as StartTime, dateadd(day,-1,tb.StartTime) as EndTime
from TempTable ta inner join TempTable tb
on ta.RowNumber+1=tb.RowNumber
union select '2005-01-01',min(dateadd(day,-1,StartTime)) from t_2 --全年第一天
union select max(dateadd(day,1,EndTime)),'2005-12-30' from t_2 --直到全年最后一天
yangfan_25845220 2010-10-27
  • 打赏
  • 举报
 回复
不好意思，题目里面给的数据，最后一条记录写错了，应该是：
2005-2-21 0:00:00 2005-2-22 0:00:00
chuifengde 2010-10-27
  • 打赏
  • 举报
 回复
2005-2-21 0:00:00 2005-2-12 0:00:00
21号下雨12号停，穿越了永远没解
SQL77 2010-10-27
  • 打赏
  • 举报
 回复
[Quote=引用楼主 yangfan_25845220 的回复:]
题目：查询2005年全年没有下雨的记录，要求用一条语句完成

返回结果：全年的没有下雨的时间区间

已知条件：
数据表已经记录了下雨的记录，每一条是以：下雨的开始时间、下雨的结束时间，来进行记录
数据表结构：StartTime EndTime
例：
2005-1-20 0:00:00 2005-1-21 0:00:00
2005-1-29 0:00:00 2005-1-30 ……
[/Quote]
日期一定不有交叉的情况吗???
十一文 2010-10-27
  • 打赏
  • 举报
 回复
关注哈 看那个高人搞定
yangfan_25845220 2010-10-27
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 fredrickhu 的回复:]

SQL code
select datediff(dd,StartTime,EndTime) from tb
[/Quote]

不好意思，可能我写的不够明白，要得到的结果结构是这样的：
2005-1-22 0:00:00 2005-1-28 0:00:00
--小F-- 2010-10-27
  • 打赏
  • 举报
 回复 
select datediff(dd,StartTime,EndTime) from tb
thegodofwar 2010-10-27
  • 打赏
  • 举报
 回复 


select t3.et1+1,t3.st2-1 from (select * from (select StartTime st1,

EndTime 

et1,rownum r1 from tb order by StartTime)t1 join 

(select StartTime st2,EndTime et2,rownum r2 from tb order by StartTime)t2 

on (r2=r1+1))t3;

Oracle实现
thegodofwar 2010-10-27
  • 打赏
  • 举报
 回复 


select t3.et1+1,t3.st2-1 from (select * from (select StartTime st1,EndTime et1,rownum r1 from tb order by StartTime)t1 join (select StartTime st2,EndTime et2,rownum r2 from tb order by StartTime)t2 on (r2=r1+1))t3;
alfred_2006 2010-10-27
  • 打赏
  • 举报
 回复
呵呵，楼上xd还真强，支持日期错位！
abuying 2010-10-27
  • 打赏
  • 举报
 回复 
--最主要是排序



--改进版

 select dateadd(day,1,t1.endtime) 开始时间,dateadd(day,-1,t2.starttime)结束时间 from 

(select *,id=(select count(1)+1  from # a where a.starttime<#.starttime) from #) t1

left join 

(select *,id=(select count(1)+1  from # a where a.starttime<#.starttime) from #) t2

 on t1.id+1=t2.id and datediff(day,t1.endtime,t2.starttime)>=2

 where t2.starttime is not null

--改进20#

SELECT dateadd(day,1,t1.EndTime) 开始时间,dateadd(day,-1,t2.StartTime) 结束时间 FROM # t1,# t2

WHERE t2.starttime>t1.starttime and  datediff(day,t1.endtime,t2.starttime)>=2 

and NOT EXISTS(SELECT 1 FROM # t3 WHERE datediff(day,t1.EndTime,t3.StartTime)>=1 and datediff(day,t3.StartTime,t2.StartTime)>=1)
加载更多回复（5）
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
面试题目的总结归纳
本篇文章来自一位很资深的前辈对于最近java面试题目所做的总结归纳，有170道题目 ，知识面很广 ，而且这位前辈对于每个题都自己测试给出了答案 ，如果你对某个题有疑问或者不明白，可以电脑端登录把题目复制下来然后...
C/C++面试题目汇总
C/C++面试题目汇总   最近因为找工作，收集了很多C语言方面方面的面试题以及答案。现在新工作搞定了，决定把这些资料发出来，送给有需要的朋友，免得再象我一样到处搜寻，实在辛苦。 发布之前先申明两点：  1 ...
Java面试笔试题大汇总(最全+详细答案)
本篇文章来自一位很资深的前辈对于最近java面试题目所做的总结归纳，有170道题目 ，知识面很广 ，而且这位前辈对于每个题都自己测试给出了答案 ，如果你对某个题有疑问或者不明白，可以电脑端登录把题目复制下来然后...
一题都看不懂，大厂的面试是真的变态......
最近我的一个读者朋友去了字节面试，来给我发信息吐槽，说字节的面试太困难了，像他这种三年经验的测试员，在技术面，居然一题都答不上来，这要多高的水平才能有资格去面试字节的测试岗位。
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章