34,576
社区成员
发帖
与我相关
我的任务
分享
TABLE:T_STA_SEQ
COLUMN:
FUSER_ID VARCHAR(20) /*这个字段存储的是访问用户的ID*/
FVISIT_TIME DATETIME /*这个字段存储的是该用户的访问时间*/
USE SSISTest;
go
IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta
CREATE TABLE ta
(
FUSER_ID int,
FVISIT_TIME DATETIME
)
go
INSERT INTO ta
select 1,'2011-07-01' union all
select 2,'2011-07-01' union all
select 3,'2011-07-02' union all
select 4,'2011-07-05' union all
select 5,'2011-07-30' union all
select 6,'2011-07-29' union all
select 7,'2011-05-11' union all
select 8,'2011-08-03' union all
select 9,'2011-08-04'
go
declare @start datetime
declare @end datetime
set @start = '2011-07-29'
set @end = '2011-08-05'
;with cte as
(
select
top 100 percent count(FUSER_ID) as thcount,
cast(FVISIT_TIME as date) as thdate from ta group by cast(FVISIT_TIME as date) order by cast(FVISIT_TIME as date)
),
cte1 as
(
select top 1
(select case when count(*)=0 then 0 else thcount end from cte where thdate=cast(@start as date) group by thcount)as thcount,
cast(@start as date) thdate
from cte
union all
select (select case when count(1)over()=0 then 0 else thcount end from cte where cte.thdate=dateadd(day,1,cte1.thdate))as thcount,
dateadd(day,1,cte1.thdate)as thdate
from cte1
where cte1.thdate<=cast(@end as date)
)
select thdate as '访问日期',isnull(thcount,0) as '访问人数' from cte1
/*
访问日期 访问人数
---------- -----------
2011-07-29 1
2011-07-30 1
2011-07-31 0
2011-08-01 0
2011-08-02 0
2011-08-03 1
2011-08-04 1
2011-08-05 0
2011-08-06 0
*/
select a.dates ,sum(case when b.FUSER_ID is null 0 else 1 end)) as pp_num from #dates a left join T_STA_SEQ b on datediff(day,cast(dates as datetime),b.FVISIT_TIME)=0 group by a.dates
declare @BDATE DATETIME,@EDATE DATETIME
SET @BDATE='2011-07-01'
SET @EDATE='2011-07-12'
SELECT convert(varchar(7),dateadd(day,number,@BDATE),120) as dates into #dates FROM master..spt_values where type='P'
and dateadd(day,number,@BDATE)<=@EDATE
select a.dates ,sum(case when b.FUSER_ID is not null 0 else 1 end)) as pp_num
from #dates a left join T_STA_SEQ b
on datediff(day,cast(dates as datetime),b.FVISIT_TIME)=0
group by a.dates
CREATE TABLE T_STA_SEQ
(
FUSER_ID int,
FVISIT_TIME DATETIME
)
go
INSERT INTO T_STA_SEQ
select 1,'2011-07-01' union all
select 2,'2011-07-01' union all
select 3,'2011-07-02' union all
select 4,'2011-07-05' union all
select 5,'2011-07-05' union all
select 6,'2011-07-16' union all
select 7,'2011-05-11' union all
select 8,'2011-07-09' union all
select 9,'2011-10-11'
go
declare @start datetime
declare @end datetime
set @start = '20110701'
set @end = '20110710'
select a.date,count(distinct b.FUSER_ID) as 访问人数
from (select convert(varchar(10),dateadd(dd,number,@start),120) date
from master..spt_values
where [type] = 'p' and number between 0 and datediff(dd,@start,@end)) a
left join T_STA_SEQ b on a.date = convert(varchar(10),b.FVISIT_TIME,120)
group by a.date
drop table T_STA_SEQ
/***************
date 访问人数
---------- -----------
2011-07-01 2
2011-07-02 1
2011-07-03 0
2011-07-04 0
2011-07-05 2
2011-07-06 0
2011-07-07 0
2011-07-08 0
2011-07-09 1
2011-07-10 0
警告: 聚合或其他 SET 操作消除了空值。
(10 行受影响)
USE SSISTest;
go
IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta
CREATE TABLE ta
(
FUSER_ID int,
FVISIT_TIME DATETIME
)
go
INSERT INTO ta
select 1,'2011-07-01' union all
select 2,'2011-07-01' union all
select 3,'2011-07-02' union all
select 4,'2011-07-05' union all
select 5,'2011-07-05' union all
select 6,'2011-07-16' union all
select 7,'2011-05-11' union all
select 8,'2011-07-09' union all
select 9,'2011-10-11'
go
declare @start datetime
declare @end datetime
set @start = '2011-07-01'
set @end = '2011-07-10'
;with cte1 as
(
select
cast(FVISIT_TIME as date) as '日期',
count(FUSER_ID) as '访问人数'
from ta
where FVISIT_TIME between @start and @end
group by cast(FVISIT_TIME as date)
)
,cte2 as
(
select distinct top 100 percent number from master..spt_values where number between 1 and 31 order by number
)
select
ltrim(year(@start))+'-'+ltrim(month(@start))+'-'+ltrim(number) as [访问日期],
isnull(cte1.[访问人数],0) as [访问人数]
from cte2
left join cte1 on cte2.number=day(cte1.[日期])
where number between day(@start) and day(@end)
/*
访问日期 访问人数
-------------------------------------- -----------
2011-7-1 2
2011-7-2 1
2011-7-3 0
2011-7-4 0
2011-7-5 2
2011-7-6 0
2011-7-7 0
2011-7-8 0
2011-7-9 1
2011-7-10 0
*/
declare @sdate datetime
declare @edate datetime
set @sdate = '2009-8-30'
set @edate = '2009-9-5'
select
dateadd(dd,num,@sdate)
from
(select isnull((select count(1) from sysobjects where id <t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate) <=@edate
/*
------------------------------------------------------
2009-08-30 00:00:00.000
2009-08-31 00:00:00.000
2009-09-01 00:00:00.000
2009-09-02 00:00:00.000
2009-09-03 00:00:00.000
2009-09-04 00:00:00.000
2009-09-05 00:00:00.000
(所影响的行数为 7 行)
*/
--功能:找出在2个日期之间的日期
--startdate:2009年9月15日 endDate:2009年10月3日
declare @startdate datetime,@enddate datetime
set @startdate='2009-08-30'
set @enddate='2009-09-05'
select convert(varchar(10),dateadd(day,number,@startdate),120)
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>0
and type='p'
/*----------
2009-08-31
2009-09-01
2009-09-02
2009-09-03
2009-09-04
2009-09-05
(6 行受影响)
/*
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/24/4587448.aspx
declare @start datetime
declare @end datetime
set @start = '20110701'
set @end = '20110710'
select a.date,count(distinct b.FUSER_ID) as 访问人数
from (select convert(varchar(10),dateadd(dd,number,@start),120) date
from master..spt_values
where [type] = 'p' and number between 0 and datediff(dd,@start,@end)) a
left join T_STA_SEQ b on a.date = convert(varchar(10),b.FVISIT_TIME,120)
group by a.date
select a.FUSER_ID,count(distinct b.FUSER_ID) as 访问人数
from (select distinct FUSER_ID from T_STA_SEQ) a
left join T_STA_SEQ b on a.FUSER_ID = b.FUSER_ID
where 日期 between '2011-07-01' and '2011-07-10'
group by a.FUSER_ID