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

Question

本帖最后由 yangfan_25845220 于 2010-10-27 11:24:46 编辑

Answer 1

select datediff(dd,StartTime,EndTime) from tb

Answer 2

我做出来了，有点笨，但是还是完成了，大家看看哪里要修改下
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     --直到全年最后一天

Answer 3

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
*/

Answer 4

--借用#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 行受影响)
*/

Answer 5

不错的思路不过最后还是要 where dateadd(d,1,a.endtime)<=dateadd(d,-1,b.starttime)否则跨年下雨会有问题。

Answer 6

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',nullselect 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

Answer 7

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

Answer 8

--一条SQL完成
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'GO select dateadd(day,1,t1.endtime) 开始时间,dateadd(day,-1,t2.starttime)结束时间 from 
(select *,id=(select count(1)+1  from # a where a.starttime<#.starttime),rn=datediff(day,starttime,endtime) from #) t1
left join 
(select *,id=(select count(1)+1  from # a where a.starttime<#.starttime),rn=datediff(day,starttime,endtime) from #) t2
 on t1.id+1=t2.id and dateadd(day,t1.rn,t1.endtime)<=t2.starttime
 where t2.starttime is not null开始时间                    结束时间
----------------------- -----------------------
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(2 行受影响)

Answer 9

SELECT t1.EndTime,t2.StartTime FROM tb t1,tb t2
WHERE NOT EXISTS(SELECT 1 FROM tb t3 WHERE t3.StartTime BETWEEN t1 EndTime AND t2.StartTime)

Answer 10

SELECT t1.EndTime,t2.StartTime FROM tb t1,tb t2
WHERE NOT EXISTS(SELECT 1 FROM tb t3 WHERE t3.StartTime>t1.EndTime AND t3.StartTime<t2.StartTime)
UNION ALL SELECT NULL,MIN(StartTime) FROM tb --添加年初到第一场雨的开始这段时间
UNION ALL SELECT MAX(EndTime),NULL FROM tb --添加最后一场雨到年末这段时间

Answer 11

--最主要是排序--改进版
 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)

Answer 12

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;

Answer 13

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实现