27,579
社区成员
发帖
与我相关
我的任务
分享
create table stationinfo
(
jwstation varchar(20),
jwnumber varchar(20)
)
insert into stationinfo
select '北京','x3510064'
union
select '天津','x3120068'
union
select '郑州','x3020023'
union
select '上海','x3012005'
create table jw_daycount
(jwstation varchar(20),
jwnumber varchar(20),
jwdatetime datetime,
jwcount int
)
insert into jw_daycount
select '北京','x3510064','2010-01-28 00:00:00',60
union
select '天津','x3120068','2010-01-28 00:00:00',52
union
select '上海','x3012005','2010-01-28 00:00:00',72
union
select '北京','x3510064','2010-01-28 01:00:00',60
union
select '天津','x3120068','2010-01-28 01:00:00',52
union
select '上海','x3012005','2010-01-28 01:00:00',72
union
select '北京','x3510064','2010-02-01 23:00:00',60
union
select '天津','x3120068','2010-02-01 23:00:00',52
union
select '上海','x3012005','2010-02-01 23:00:00',72
union
select '郑州','x3020023','2010-01-01 23:00:00',72
declare @startdate datetime,@enddate datetime,@i int,@j int
declare @temptable table(jwstation varchar(20),jwnumber varchar(20),jwdatetime datetime)
select @startdate='2010-01-28',@enddate='2010-02-01'
select @i=0,@j=DATEDIFF(day,@startdate,@enddate)
while(@i<=@j)
begin
insert into @temptable
select jwstation,jwnumber,DATEADD(day,@i,@startdate) from stationinfo
set @i=@i+1
end
declare @jwdaycount table (jwnumber varchar(20),jwdatetime datetime,jwcount int)
insert into @jwdaycount(jwnumber,jwdatetime,jwcount)
select jwnumber,CONVERT(varchar(10),jwdatetime,120),SUM(jwcount) from jw_daycount group by jwnumber,CONVERT(varchar(10),jwdatetime,120)
select temptable.jwstation,temptable.jwnumber,temptable.jwdatetime,isnull(jwdaycount.jwcount,0)jwcount
from @temptable temptable
left join @jwdaycount jwdaycount on jwdaycount.jwnumber=temptable.jwnumber and jwdaycount.jwdatetime=temptable.jwdatetime
order by temptable.jwdatetime,temptable.jwnumber
--假如楼主你可以确定只有郑州才会没有数据的话,我有个方法
--假如楼主你的jwdatetime的数据类型是时间格式可以这样写
select convert(char(13),jwdatetime,120) as 时间点,count(jwstation) as 站点数
from jw_daycount
group by convert(char(13),jwdatetime,120)
--这样出来的结果就会像这样
2010-01-28 00 | 4 --四个站点有数据
2010-01-28 01 | 4 --四个站点有数据
2010-01-28 02 | 3 --三个站点有数据
2010-01-28 03 | 3 --三个站点有数据
--假如楼主你的jwdatetime的数据类型是字符串的话可以这样写
--字符串格式是:'2010-01-28 12'
select jwdatetime 时间点,count(jwstation) as 站点数
from jw_daycount
group by jwdatetime
--把上面的语句的结果作为子查询然后查询,请看:
select 时间点
from (
select convert(char(13),jwdatetime,120) as 时间点,count(jwstation) as 站点数
from jw_daycount
group by convert(char(13),jwdatetime,120)
) a
where 站点数 < 4
--这样就得到了没有郑州数据的时间点了