求SQL语句写法

[clarck_913]

大家好，我现在遇到了一点问题，需要一条SQL语句。

表结构大致是这样的

TABLE：T_STA_SEQ

COLUMN:
FUSER_ID VARCHAR(20) /*这个字段存储的是访问用户的ID*/
FVISIT_TIME DATETIME /*这个字段存储的是该用户的访问时间*/

用户只要访问一次，就会记录下一条记录，不管FUSER_ID是否重复

现在我要做的事情是，查询某个日期时间段（比如从2011年07月01日到2011年07月10日）访问的人数。

我现在做的方法是将时间字段转换成字符串格式，然后对其group by

这样，每个日期的访问人数就查出来了。

但是这样做有一个问题，就是，如果2011年07月02日没有用户访问，我希望给用户显示的是

2011-07-02 : 0

但实际上SQL语句查询的结果是没有这条记录的

不知道怎么查询了

如果情况很极端，这10天每天都没有用户访问，那么我想给的结果是

2011-07-01：0
2011-07-02：0
......
2011-07-10：0

不知道有没有SQL可以实现。

谢谢大家

[clarck_913]

别告诉我用SQL排序

然后在程序里去补全空着的日期哦

[AcHerat]

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

[Zoezs]

做一张日期表然后left join一下。

[AcHerat]

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

[clarck_913]

[Quote=引用 3 楼 zoezs 的回复:]

做一张日期表然后left join一下。
[/Quote]

你这回答很极品~~~

[--小F--]

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

[--小F--]

6楼得到时间表 与你的表left join 然后count(distinct xx)

[DataBox-MDX]

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
*/

[AcHerat]

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 行受影响)

[wujianfeng32]

我觉得没办法，你的弄一个日期关联档。。

[equn93]

小三的方案不错啊
关键是要巧用master..spt_values
获取一段时间内的所有日期
然后再关联查询。

[oO寒枫Oo]

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

[oO寒枫Oo]

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 

[DataBox-MDX]

支持跨月查询

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

*/

[clarck_913]

我想问问有木有通用的写法

如果不是SQLServer呢？

[AcHerat]

[Quote=引用 15 楼 clarck_913 的回复:]

我想问问有木有通用的写法

如果不是SQLServer呢？
[/Quote]

不是SQLServer肯定不能用楼上的那些了，不同软件怎么会有通用的写法，ORACLE和SQL写法肯定不一样，楼主是要SQL的还是MYSQL或ORACLE的，通用木办法。