求高难度sql语句，百思不得其解

Question

如下电视节目播出表频道,开播时间(时),分,秒，播出时长(秒),星期
----------------------------------------------
CCTV_1,8          ,10 ,00 ,6000        ,1
CCTV_1,8          ,10 ,00 ,6000        ,2
CCTV_1,8          ,10 ,00 ,6000        ,3
CCTV_1,8          ,10 ,00 ,6000        ,4
那么我要查出2007-1-1 10:10:20到
2007-1-20 10:10:30的CCTV_1的播出总时长
要自动根据这个时间范围内的星期去判断得出如下样式表频道,播出时长(秒)
----------------------------------------------
CCTV_1,8000000

Answer 1

很简单啊,用 weekday()函数将日期转换成星期几即可.其返回值为1时,即为星期天,1为星期一,7为期星六
然后将其带回SQL进行查询即可

Answer 2

给你点思路，嵌套查询，首先将日期时间部分组合成日期类型，再查询，如下（没有测试，你可以理解思路）
select sum(播出时长) from 
(
   select *,cast(cast(年 as nvarchar(4))+'-'+cast(月 as nvarchar(2))+'-'+……
                 +cast(时 as nvarchar(2))+':'+cast(秒 as nvarchar(2)) as DateTime) as 播出时间
) aa
where 播出时间 between '2007-1-1 10:30:00' And '2007-1-5 12:30'

Answer 3

不是就是星期几，能我没表述明白
意思是说比如数据库中只有cctv_1在星期1的八点整播出20分钟节目那么我要查询2007-1-1 8:15:00 至 2007-1-30 9:00:00这段时间的播出时长
这个区间内有五个星期一那么就是5*20分钟,但是起始那一天是从8:15开始的所以要减去15分钟。
最后就应该是85分钟的播出时长

Answer 4

如果是我就压根连存储过程都不耐烦用,对应记录也就那7条,读进内存里用代码慢慢算去...写起来容易运行起来也不慢,何苦限死要用Sql来做?

Answer 5

用了一个中午的时间设计了一下,应该可以.
里面还考虑了,在同一天可能有两个时段播出的情况
建表:
create table channelplay
(
channelname varchar(40) not null,
playhour int not null,
playminute int not null,
playsecond int not null,
playseconds bigint not null,
onweek int not null
)
goinsert into channelplay values('cctv1', 8,10,0,6000,1)
go
insert into channelplay values('cctv1', 14,10,0,6000,1)
go
insert into channelplay values('cctv1', 8,10,0,6000,2)
go
insert into channelplay values('cctv1', 8,10,0,6000,3)
go
insert into channelplay values('cctv1', 8,10,0,6000,4)
go

Answer 6

我计算的思路是
2007-1-1至2007-1-20符合要求的日期数分别成上他们当天的有效播放时长得到他们的理想的总播放时长,然后再减去起始日期(2007-1-1)的无效播放时间再减去结束日期(2007-1-20)的无效播放时长得到真实的播放时长这里是获取起始日期的无效播放时间的语句(用SQL1表示)
select isnull(sum(
case when convert(datetime , '2007-01-01 10:10:20') >
convert(datetime , '2007-01-01 ' +
convert(varchar,playhour)+':'+
convert(varchar,playminute)+':'+
convert(varchar,playsecond)
)
then 
    case when convert(datetime , '2007-01-01 10:10:20') <
DATEADD(ss,playseconds,
convert(datetime , '2007-01-01 ' +
convert(varchar,playhour)+':'+
convert(varchar,playminute)+':'+
convert(varchar,playsecond))
) 
    then  DATEDIFF(ss,
convert(datetime , '2007-01-01 ' +
convert(varchar,playhour)+':'+
convert(varchar,playminute)+':'+
convert(varchar,playsecond)
  ),
convert(datetime , '2007-01-01 10:10:20')
)
    else playseconds end
else  0 end
),0)
from channelplay
where onweek = DATEPART(dw,convert(datetime,'20070101',112))这里是获取结束的无效播放时长的语句(用SQL2表示)
select isnull(sum(
case when convert(datetime , '2007-01-20 10:10:30') <
DATEADD(ss,playseconds,
convert(datetime , '2007-01-20 ' +
convert(varchar,playhour)+':'+
convert(varchar,playminute)+':'+
convert(varchar,playsecond)
) 
)
then 
    case when convert(datetime , '2007-01-20 10:10:30') >
convert(datetime , '2007-01-20 ' +
convert(varchar,playhour)+':'+
convert(varchar,playminute)+':'+
convert(varchar,playsecond)
)
    then  DATEDIFF(ss,convert(datetime , '2007-01-20 10:10:30'),
DATEADD(ss,playseconds,
convert(datetime , '2007-01-20 ' +
convert(varchar,playhour)+':'+
convert(varchar,playminute)+':'+
convert(varchar,playsecond)
) 
)
)
    else playseconds end
else  0 end
),0)
from channelplay
where onweek = DATEPART(dw,convert(datetime,'20070120',112))

Answer 7

这里是获取起始日期至截止日期在周1至周7的时间分配情况,也就是有多少个周1多少个周2....多少个周7(用SQL3表示)
select 
DATEPART(dw,convert(datetime,'20070101',112)) w,
(DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)/7 + case when (DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)%7 >=1 then 1 else 0 end num
union
select 
DATEPART(dw,DATEADD(dd,1,convert(datetime,'20070101',112))) w,
(DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)/7 + case when (DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)%7 >=2 then 1 else 0 end num
union
select 
DATEPART(dw,DATEADD(dd,2,convert(datetime,'20070101',112))) w,
(DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)/7 + case when (DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)%7 >=3 then 1 else 0 end num
union
select 
DATEPART(dw,DATEADD(dd,3,convert(datetime,'20070101',112))) w,
(DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)/7 + case when (DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)%7 >=4 then 1 else 0 end num
union
select 
DATEPART(dw,DATEADD(dd,4,convert(datetime,'20070101',112))) w,
(DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)/7 + case when (DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)%7 >=5 then 1 else 0 end num
union
select 
DATEPART(dw,DATEADD(dd,5,convert(datetime,'20070101',112))) w,
(DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)/7 + case when (DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)%7 >=6 then 1 else 0 end num
union
select 
DATEPART(dw,DATEADD(dd,6,convert(datetime,'20070101',112))) w,
(DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)/7 + case when (DATEDIFF(dd,convert(datetime,'20070101',112),convert(datetime,'20070120',112))+1)%7 >=7 then 1 else 0 end num最后把这些语句都关联起来进行计算得到总时长(下面的SQL1,SQL2,SQL3分别用上面的语句代替)
select a.channelname,
isnull(sum(playseconds * num),0)-(SQL1)-(SQL2)
from channelplay a,(SQL3) b
where a.onweek = b.w
group by a.channelname

Answer 8

请教大师
假如我要加入查询条件比如说 我要查 cctv8 台 2008-10-10 08:00:00至2008-10-20 08:00:00的播出时长，那就要把上面的所有查询条件都替换掉么?

Answer 9

在程序上应该设定4个参数
开始日期 begindate = '20070101'
开始时间 begintime = '10:10:20'
结束日期 enddate = '20070120'
结束时间 endtime = '10:10:30'
写语句的时候,上面对应的时间你分别用这些参数替换调,
真正使用的时候,对这些参数赋值就是了上面只是为了方便,直接写了硬性的时间,你如果需要临时查询,直接替换时间也可以