求统计今日IP/今日PV/昨日IP/昨日PV的高效语句

[SK猫]

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好像没效果，望各位指点一下，谢谢

[jinjazz]

[Quote=引用 6 楼 claro 的回复:]
引用 3 楼 jinjazz 的回复:
这个东西很适合用ssas来做..
说说
[/Quote]

http://blog.csdn.net/jinjazz/archive/2009/04/07/4053719.aspx

[jinjazz]

顺便说一下，当前在线人数不是这么做的，都是做缓存计数的。

[百年树人]

select 
  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()) 

这样？

[cyz9977]

--昨天
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

[jinjazz]

这个东西很适合用ssas来做..

[SK猫]

[Quote=引用 1 楼 josy 的回复:]
SQL codeselectCONVERT(VARCHAR(30),sTime,111)as日期,count(*)ascount1,sum(sCount)ascount2fromuestc_statwheresWebsiteId=542andsTime>=CONVERT(VARCHAR(30),getdate()-1,111)groupbyCONVERT(VARCHAR(30),sTime,111)unionallselect'当前在线',count(distinctsIp),count(1)fromuestc_statwheresWebsiteId=542andsLeaveTime>=DateAdd(n,-10,getdate())
这样？
[/Quote]

这样出来只有一条记录

日期 count1 count2
------------------------------ ----------- -----------
当前在线 0 0

[SK猫]

[Quote=引用 2 楼 cyz9977 的回复:]
SQL code--昨天selectcount(*)ascount1,sum(sCount)ascount2fromuestc_statwheresWebsiteId=542anddatediff(dd, sTime,getdate())=-1--今天selectcount(*)ascount1,sum(sCount)ascount2fromuestc_statwheresWebsiteId=542anddatediff(dd, sTime,getdate())=0
[/Quote]


datediff(dd, sTime,getdate())=-1 这样看起来清晰多了，不过在效率上还是没有提高

谢谢你，谢谢楼上各位

[claro]

[Quote=引用 3 楼 jinjazz 的回复:]
这个东西很适合用ssas来做..
[/Quote]说说

[claro]

帮顶。

[ChinaJiaBing]

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

[taoistong]

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) 

给分