22,210
社区成员
发帖
与我相关
我的任务
分享
declare @startTime datetime
,@endTime datetime
set @startTime='2010-12-16 10:00:00'
set @endTime='2010-12-16 10:10:00'
SELECT x.[siteId],x.[adid],count(x.ip) as ip
from
(
SELECT A.[SiteId],A.[AdId],A.[Ip] FROM PEE_DATA_Show_20101216 A WHERE A.[RecTime]>=@startTime AND A.[RecTime]<@endTime
GROUP BY A.[SiteId],A.[AdId],A.[Ip]
EXCEPT
SELECT A1.[AdId],A1.[SiteId],A1.[Ip] FROM PEE_DATA_Show_20101216 A1 WHERE A1.[RecTime]<@startTime
GROUP BY A1.[AdId],A1.[SiteId],A1.[Ip]
) x
group by x.[siteId],x.[adid]
.....
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
insert into PEE_DATA_Show_20101216
select 1,1,'1.1.1.1','2010-12-16 9:28:28'--在starttime前,不计
union all select 1,1,'10.10.10.10','2010-12-16 9:29:00' --这种情况
union all select 1,1,'10.10.10.10','010-12-16 10:02:18'
union all select 1,1,'10.10.10.101','010-12-16 10:05:28'
union all select 1,1,'10.10.10.10','010-12-16 10:06:28' ---IP与前面相同
union all select 1,1,'10.10.101.10','010-12-16 10:10:28' -- 在endtime后,不计
/* 我想要的是这种结果。因为IP一天的只能算一个
siteId adid ip
----------- ----------- -----------
1 1 1
*/
create table PEE_DATA_Show_20101216(siteid int,adid int,ip nvarchar(15),RecTime datetime)
insert into PEE_DATA_Show_20101216 select 1,1,'1.1.1.1','2010-12-16 9:28:28'--在starttime前,不计
union all select 1,1,'10.10.10.10','010-12-16 10:02:18'
union all select 1,1,'10.10.10.101','010-12-16 10:05:28'
union all select 1,1,'10.10.10.10','010-12-16 10:06:28' ---IP与前面相同
union all select 1,1,'10.10.101.10','010-12-16 10:10:28' -- 在endtime后,不计
go
declare @startTime datetime
,@endTime datetime
set @startTime='2010-12-16 10:00:00'
set @endTime='2010-12-16 10:10:00'
SELECT x.[siteId],x.[adid],count(x.ip) as ip
from
(
SELECT distinct [SiteId],[AdId],[Ip] FROM PEE_DATA_Show_20101216 A WHERE A.[RecTime]>=@startTime AND A.[RecTime]<@endTime
) x
group by x.[siteId],x.[adid]
go
drop table PEE_DATA_Show_20101216
/*
siteId adid ip
----------- ----------- -----------
1 1 2
(1 行受影响)
*/
WHERE A.[RecTime]>=@startTime AND A.[RecTime]<@endTime
SELECT x.[siteId],x.[adid],count(x.ip) as ip
from
(
SELECT distinct [SiteId],[AdId],[Ip] FROM PEE_DATA_Show_20101216 A WHERE A.[RecTime]>=@startTime AND A.[RecTime]<@endTime
) x
group by x.[siteId],x.[adid]