sId sWebsiteId sTime sLeaveTime sIp sCount
-------------------- ----------- ----------------------- ----------------------- --------------- -----------
1 542 2008-11-18 09:18:35.000 2008-11-18 14:51:29.000 61.183.248.218 87
2 542 2008-11-18 09:38:36.000 2008-11-18 17:04:23.000 61.144.207.115 128
3 543 2008-11-18 09:42:35.000 2008-11-18 10:36:46.000 61.183.248.218 5
4 552 2008-11-18 16:45:19.000 2008-11-18 16:45:21.000 61.144.207.115 4
5 551 2008-11-18 16:45:54.000 2008-11-18 16:45:55.000 61.144.207.115 5
7 549 2008-11-18 16:46:58.000 2008-11-18 16:46:59.000 61.144.207.115 3
8 548 2008-11-18 16:47:15.000 2008-11-18 16:47:16.000 61.144.207.115 4
表结构如上图,sTime是开始访问时间,sLeaveTime是最后访问时间,sCount是这个IP访问页面数量,还有浏览器、分辨率、来路页面的字段,这里就不列出来了现在要统计 今日IP / 今日PV / 昨日IP / 昨日PV / 当前在线人数今日IP/今日PV
select count(*) as count1,sum(sCount) as count2 from uestc_stat where sWebsiteId=542 and sTime>=CONVERT(VARCHAR(30),getdate(),111)昨日IP/昨日PV
select count(*) ,sum(sCount) from uestc_stat where sWebsiteId=542 and sTime>=CONVERT(VARCHAR(30),dateadd(day,-1,getdate()),111) and sTime<CONVERT(VARCHAR(30),getdate(),111)当前在线
select count(distinct sIp),count(1) from uestc_stat where sWebsiteId=542 and sLeaveTime>= DateAdd(n,-10,getdate())
现在要3条语句,能否弄成一条,且效率高的,UNION好像没效果,望各位指点一下,谢谢
-------------------- ----------- ----------------------- ----------------------- --------------- -----------
1 542 2008-11-18 09:18:35.000 2008-11-18 14:51:29.000 61.183.248.218 87
2 542 2008-11-18 09:38:36.000 2008-11-18 17:04:23.000 61.144.207.115 128
3 543 2008-11-18 09:42:35.000 2008-11-18 10:36:46.000 61.183.248.218 5
4 552 2008-11-18 16:45:19.000 2008-11-18 16:45:21.000 61.144.207.115 4
5 551 2008-11-18 16:45:54.000 2008-11-18 16:45:55.000 61.144.207.115 5
7 549 2008-11-18 16:46:58.000 2008-11-18 16:46:59.000 61.144.207.115 3
8 548 2008-11-18 16:47:15.000 2008-11-18 16:47:16.000 61.144.207.115 4
表结构如上图,sTime是开始访问时间,sLeaveTime是最后访问时间,sCount是这个IP访问页面数量,还有浏览器、分辨率、来路页面的字段,这里就不列出来了现在要统计 今日IP / 今日PV / 昨日IP / 昨日PV / 当前在线人数今日IP/今日PV
select count(*) as count1,sum(sCount) as count2 from uestc_stat where sWebsiteId=542 and sTime>=CONVERT(VARCHAR(30),getdate(),111)昨日IP/昨日PV
select count(*) ,sum(sCount) from uestc_stat where sWebsiteId=542 and sTime>=CONVERT(VARCHAR(30),dateadd(day,-1,getdate()),111) and sTime<CONVERT(VARCHAR(30),getdate(),111)当前在线
select count(distinct sIp),count(1) from uestc_stat where sWebsiteId=542 and sLeaveTime>= DateAdd(n,-10,getdate())
现在要3条语句,能否弄成一条,且效率高的,UNION好像没效果,望各位指点一下,谢谢
CONVERT(VARCHAR(30),sTime,111) as 日期,
count(*) as count1,
sum(sCount) as count2
from uestc_stat
where sWebsiteId=542 and sTime>=CONVERT(VARCHAR(30),getdate()-1,111)
group by CONVERT(VARCHAR(30),sTime,111)
union all
select
'当前在线',
count(distinct sIp),
count(1)
from uestc_stat
where sWebsiteId=542 and sLeaveTime>= DateAdd(n,-10,getdate())
这样?
--昨天
select count(*) as count1,sum(sCount) as count2 from uestc_stat where sWebsiteId=542 and datediff(dd, sTime,getdate())=-1 --今天
select count(*) as count1,sum(sCount) as count2 from uestc_stat where sWebsiteId=542 and datediff(dd, sTime,getdate())=0
这样出来只有一条记录日期 count1 count2
------------------------------ ----------- -----------
当前在线 0 0
select COUNT(*) as count1,SUM(scount) as count2,当前ip=(select COUNT(sid) from @表 where sleavetime>=dataadd(s,-10,GETDATE())),
当前人前=(select COUNT(scount) from @表>sleavetime>=dateadd(s,-10,getdate())) from @表 a
where swebsiteid='542' and stime>=DATEadd(day,-1,GETDATE())
group by stime
select count(case when sTime>=CONVERT(char(10),getdate(),120) then 1 else 0 end ) as 今日IP
,sum(case when sTime>=CONVERT(char(10),getdate(),120) then sCount else 0 end ) as 今日PV
,count( case when sTime>=CONVERT(char(10),dateadd(day,-1,getdate()),120) and sTime <CONVERT(char(10),getdate(),120) then 1 else 0 end) 昨日IP
,sum(case when sTime>=CONVERT(char(10),dateadd(day,-1,getdate()),120) and sTime <CONVERT(char(10),getdate(),120) then sCount else 0 end) 昨日PV
,(select count(distinct sIp) from uestc_stat where sWebsiteId=542 and sLeaveTime>= DateAdd(n,-10,getdate())) 当前在线IP
,count(case when sLeaveTime>= DateAdd(n,-10,getdate()) then 1 else 0 end ) 当前在线人数
from uestc_stat
where sWebsiteId=542 and sTime>=CONVERT(CHAR(10),getdate()-1,120)
给分
http://blog.csdn.net/jinjazz/archive/2009/04/07/4053719.aspx