我首先通过查询得到一张表,类似如下
channels date
cctv1 2007-10-12
cctv1 2007-10-13
cctv2 2007-10-10
cctv3 2007010-12
现在要求输入一个时间段,比如@startDate = '20071010' @endDate = '20071014',则我需要得到没有频道在这个时间段中没有时间纪录的信息,如下
channels missingdate
cctv1 2007-10-10
cctv1 2007-10-11
cctv1 2007-10-14
cctv2 2007-10-11
cctv2 2007-10-12
cctv2 2007-10-13
cctv2 2007-10-14
cctv3 2007-10-10
cctv3 2007-10-11
cctv3 2007-10-13
cctv3 2007-10-14这个Select查询语句如何写呀??
拜托各位了,很着急!
channels date
cctv1 2007-10-12
cctv1 2007-10-13
cctv2 2007-10-10
cctv3 2007010-12
现在要求输入一个时间段,比如@startDate = '20071010' @endDate = '20071014',则我需要得到没有频道在这个时间段中没有时间纪录的信息,如下
channels missingdate
cctv1 2007-10-10
cctv1 2007-10-11
cctv1 2007-10-14
cctv2 2007-10-11
cctv2 2007-10-12
cctv2 2007-10-13
cctv2 2007-10-14
cctv3 2007-10-10
cctv3 2007-10-11
cctv3 2007-10-13
cctv3 2007-10-14这个Select查询语句如何写呀??
拜托各位了,很着急!
解决方案 »
- 求sql的查询语句关于车票预售系统
- 今天把我刚开始接触sql的简单事例总结了一下,全是从以前收藏的帖子翻出来的,供初学者来学习,希望能有所帮助,有空会继续总结
- ----------SQL------update------
- Windows 8 安装SQLManagementStudio_x86_CHS 提示不兼容,完全无法继续!
- 信息同步的问题.+++++++++++++++++++++++++++++标题要长
- 谢谢
- 70分!能否用一个SQL语句完成这个分组查询(我试了好几种方法都不行)?
- 请教问题(有关 Access 的 SQL 语句)
- 有关数据库复制权限的问题-(在线等待)
- 如何截取无规则的字段??
- 将 varchar 值 '1,2,3,4,5,6' 转换为数据类型为 int 的列时发生语法错误。 按网上的办法没能解决
- 老问题:sql语句表示关系代数里的除法
insert @T select 'cctv1', '2007-10-12'
insert @T select 'cctv1', '2007-10-13'
insert @T select 'cctv2', '2007-10-10'
insert @T select 'cctv3', '2007-10-12'
declare @startDate datetime,@endDate datetime
select @startDate = '20071010', @endDate ='20071014'declare @ta table(tdate nvarchar(10))
while @startDate!>@endDatebegin
insert @ta select convert(varchar(10),@startDate,120)
set @startDate=dateadd(d,1,@startDate)
endselect
distinct t.channels,t2.[tdate]
from
@T t cross join @ta t2
where
not exists(select 1 from @t where channels=t.channels and [date]=t2.tdate)
order by channels
channels tdate
-------- ----------
cctv1 2007-10-10
cctv1 2007-10-11
cctv1 2007-10-14
cctv2 2007-10-11
cctv2 2007-10-12
cctv2 2007-10-13
cctv2 2007-10-14
cctv3 2007-10-10
cctv3 2007-10-11
cctv3 2007-10-13
cctv3 2007-10-14(所影响的行数为 11 行)
select
t.channels,t2.[tdate]
from
@T t cross join @ta t2
where
not exists(select 1 from @t where channels=t.channels and [date]=t2.tdate)
group by t.channels,t2.[tdate]
order by channels
declare @T table(channels nvarchar(5), [date] datetime)
insert @T select 'cctv1', '2007-10-12'
insert @T select 'cctv1', '2007-10-13'
insert @T select 'cctv2', '2007-10-10'
insert @T select 'cctv3', '2007-10-12'
declare @startDate datetime,@endDate datetime
select @startDate = '20071010', @endDate ='20071014'declare @ta table(tdate nvarchar(10))
while @startDate!>@endDatebegin
insert @ta select convert(varchar(10),@startDate,120)
set @startDate=dateadd(d,1,@startDate)
endselect
t.channels,t2.[tdate]
from
@T t cross join @ta t2
where
tdate not in (select [date] from @t where channels=t.channels )--------not in
group by
t.channels,t2.[tdate]
order by channels
insert into tb values('cctv1','2007-10-12')
insert into tb values('cctv1','2007-10-13')
insert into tb values('cctv2','2007-10-10')
insert into tb values('cctv3','2007-10-12')
go
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-10-10'
set @dt2 = '2007-10-14'select top 100 id=identity( int,0,1) into tmp from syscolumns a,syscolumns bselect distinct a.channels,convert(varchar(10),dateadd(day,b.id,@dt1),120) date from tmp b cross join tb a
where dateadd(day,b.id,@dt1) <= @dt2 and a.channels+convert(varchar(10),dateadd(day,b.id,@dt1),120) not in
(select channels + convert(varchar(10),date,120) from tb)drop table tb,tmp/*
channels date
---------- ----------
cctv1 2007-10-10
cctv1 2007-10-11
cctv1 2007-10-14
cctv2 2007-10-11
cctv2 2007-10-12
cctv2 2007-10-13
cctv2 2007-10-14
cctv3 2007-10-10
cctv3 2007-10-11
cctv3 2007-10-13
cctv3 2007-10-14(所影响的行数为 11 行)
*/
http://www.zsn123.cn
select @startDate = '2007-10-10', @endDate ='2007-10-14'
select d.*,(a.i + b.i* 10 + c.i* 100) + @startDate as missingdate
from
(
select 0 as i
union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
) a
,
(
select 0 as i
union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
) b
,
(
select 0 as i
union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
) c
,
(
select distinct channels
from T
) d
where (a.i + b.i* 10 + c.i* 100) + @startDate <= @endDate
and
not exists
(
select 1
from T
where channels = d.channels
and
(a.i + b.i* 10 + c.i* 100) + @startDate = [date]
)
order by d.channels,missingdate