select b.city,datepart(hh,a.logtime) as hh,isnull(count(b.city),0) as num from smsrec a join
(
select distinct * from areacode where city in
('青岛', '烟台', '威海', '日照', '济南', '淄博', '德州', '潍坊', '济宁', '泰安', '临沂', '滨州', '东营', '枣庄', '莱芜', '聊城', '菏泽')
) b
on left(a.手机号,4)=b.区号
where datediff(d,a.logtime,getdate())=1
group by b.city,datepart(hh,a.logtime)
order by city,hh我要的结果是: 地市1 0点 数量
.......
地市1 23点 数量
地市2 0点 数量
........
地市2 23点 数量
........但,我统计出来的数据为0的确不显示,请问各位高手该怎么写?
(
select distinct * from areacode where city in
('青岛', '烟台', '威海', '日照', '济南', '淄博', '德州', '潍坊', '济宁', '泰安', '临沂', '滨州', '东营', '枣庄', '莱芜', '聊城', '菏泽')
) b
on left(a.手机号,4)=b.区号
where datediff(d,a.logtime,getdate())=1
group by b.city,datepart(hh,a.logtime)
order by city,hh我要的结果是: 地市1 0点 数量
.......
地市1 23点 数量
地市2 0点 数量
........
地市2 23点 数量
........但,我统计出来的数据为0的确不显示,请问各位高手该怎么写?
from smsrec
a join
(
select distinct *
from areacode
where city in
('青岛', '烟台', '威海', '日照', '济南', '淄博', '德州', '潍坊', '济宁', '泰安', '临沂', '滨州', '东营', '枣庄', '莱芜', '聊城', '菏泽')) b
on left(a.手机号,4)=b.区号
where datediff(d,a.logtime,getdate())=1 and datepart(hh,a.logtime)>0--大于0
group by b.city,datepart(hh,a.logtime)
having
order by city,hh
sys_SegmentList 手机地区表我要获得 每个地市 每个运营商 每个时段 的上行数量 而且如果没有上行数据的以0显示
地市1 移动 0点 数量
.......
地市1 连通 23点 数量
地市2 移动 0点 数量
........
地市2 连通 23点 数量
sys_SegmentList手机地区表 地区 地区手机号段
From
(
Select A.hh, B.city
From
(
Select 0 As hh
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
Union All Select 10
Union All Select 11
Union All Select 12
Union All Select 13
Union All Select 14
Union All Select 15
Union All Select 16
Union All Select 17
Union All Select 18
Union All Select 19
Union All Select 20
Union All Select 21
Union All Select 22
Union All Select 23
) A
Cross Join
(
select distinct city, 区号 from areacode where city in
('青岛', '烟台', '威海', '日照', '济南', '淄博', '德州', '潍坊', '济宁', '泰安', '临沂', '滨州', '东营', '枣庄', '莱芜', '聊城', '菏泽')
) B
) C
Left Join smsrec D
On Left(D.手机号,4) = C.区号 And Datepart(hh, D.logtime) = C.hh
And Datediff(d, D.logtime, Getdate()) = 1
Group By D.city, D.hh
Order By D.city, D.hh
From
(
Select A.hh, B.city
From
(
Select 0 As hh
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
Union All Select 10
Union All Select 11
Union All Select 12
Union All Select 13
Union All Select 14
Union All Select 15
Union All Select 16
Union All Select 17
Union All Select 18
Union All Select 19
Union All Select 20
Union All Select 21
Union All Select 22
Union All Select 23
) A
Cross Join
(
select distinct city, 区号 from areacode where city in
('青岛', '烟台', '威海', '日照', '济南', '淄博', '德州', '潍坊', '济宁', '泰安', '临沂', '滨州', '东营', '枣庄', '莱芜', '聊城', '菏泽')
) B
) C
Left Join smsrec D
On Left(D.手机号,4) = C.区号 And Datepart(hh, D.logtime) = C.hh
And Datediff(d, D.logtime, Getdate()) = 1
Group By D.city, D.hh
Order By D.city, D.hh