求一条统计sql语句

[lvxianda]

有user和task 2张表

想统计一个数据每天不重复的用户登入的次数和具体列表
user表 包括 taskID，UserName。
task表 包括 taskID，loginTime。
数据
user表
username taskID
张三 1
张三 2
张三 3
李四 4

task表
taskID loginTime
1 2012-12-23 11:30:00
2 2012-12-23 18:30:00
3 2012-12-25 11:30:00
4 2012-12-23 11:00:00

结果按照天来显示，列名有 日期，人数和具体的username.
日期 人数 登入人
2012-12-23 2 张三，李四
2012-12-24 0 无
2012-12-25 1 张三

[--小F--]

;with f as
(
select
  convert(varchar(10),b.loginTime,120) as 日期,isnull(count(distinct a.username),0) as 人数,a.username as 登入人
from
  user a left join task b
on
  a.taskID=b.taskID
)
select
 日期,人数, [登入人]=stuff((select ','+[value] from f where 日期=t.日期 for xml path('')), 1, 1, '') 
from
 f t 
group by
 日期,人数

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-26 17:18:06
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[user]
if object_id('[user]') is not null drop table [user]
go 
create table [user]([username] varchar(4),[taskID] int)
insert [user]
select '张三',1 union all
select '张三',2 union all
select '张三',3 union all
select '李四',4
--> 测试数据：[task]
if object_id('[task]') is not null drop table [task]
go 
create table [task]([taskID] int,[loginTime] datetime)
insert [task]
select 1,'2012-12-23 11:30:00' union all
select 2,'2012-12-23 18:30:00' union all
select 3,'2012-12-25 11:30:00' union all
select 4,'2012-12-23 11:00:00'
--------------开始查询--------------------------
;with f as
(
select
  convert(varchar(10),b.loginTime,120) as 日期,isnull(count(distinct a.username),0) as 人数,a.username as 登入人
from
  [user] a left join task b
on
  a.taskID=b.taskID
group by
  convert(varchar(10),b.loginTime,120),a.username
)
select
 日期,人数, [登入人]=stuff((select ','+[登入人] from f where 日期=t.日期 for xml path('')), 1, 1, '') 
from
 f t 
group by
 日期,人数

----------------结果----------------------------
/* 日期         人数          登入人
---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2012-12-23 1           李四,张三
2012-12-25 1           张三

(2 行受影响)

*/




24号的还需要用master..spt_values表来构造时间  然后left join 就不写了

[--小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

构造时间的方法在这里

[快溜]

create table [user](username varchar(10),taskid int)
insert into [user]
select '张三',1 union
select '张三',2 union
select '张三',3 union
select '李四',4
create table task(taskid int,loginTime datetime)
insert into task
select 1,'2012-12-23 11:30:00' union
select 2,'2012-12-23 18:30:00' union
select 3,'2012-12-25 11:30:00' union
select 4,'2012-12-23 11:00:00' 

declare @bdate datetime=(select min(loginTime) from task),
        @edate datetime=(select MAX(loginTime) from task)
;with cte1 as
(
  select 日期=DATEADD(day,number,@bdate) from master.dbo.spt_values
     where type='p' and number<=DATEDIFF(DAY,@bdate,@edate)
 )
 ,cte2 as
 (
   select a.username,b.loginTime from [user] a,task b where a.taskid=b.taskid
 )
 
select 日期,
       人数=(select COUNT(distinct username) from cte2 where DATEDIFF(DAY,a.日期,loginTime)=0),
       登入人=isnull(stuff((select distinct ','+username from cte2 
             where DATEDIFF(DAY,a.日期,loginTime)=0 for xml path('')),1,1,''),'无')
from cte1 a      

/*
日期                      人数          登入人
----------------------- ----------- -------------
2012-12-23 11:00:00.000 2           李四,张三
2012-12-24 11:00:00.000 0           无
2012-12-25 11:00:00.000 1           张三         

[快溜]

create table [user](username varchar(10),taskid int)
insert into [user]
select '张三',1 union
select '张三',2 union
select '张三',3 union
select '李四',4
create table task(taskid int,loginTime datetime)
insert into task
select 1,'2012-12-23 11:30:00' union
select 2,'2012-12-23 18:30:00' union
select 3,'2012-12-25 11:30:00' union
select 4,'2012-12-23 11:00:00' 

declare @bdate datetime=(select min(loginTime) from task),
        @edate datetime=(select MAX(loginTime) from task)
;with cte1 as
(
  select 日期=DATEADD(day,number,@bdate) from master.dbo.spt_values
     where type='p' and number<=DATEDIFF(DAY,@bdate,@edate)
 )
 ,cte2 as
 (
   select a.username,b.loginTime from [user] a,task b where a.taskid=b.taskid
 )
 
select 日期=convert(varchar(10),日期,120),
       人数=(select COUNT(distinct username) from cte2 where DATEDIFF(DAY,a.日期,loginTime)=0),
       登入人=isnull(stuff((select distinct ','+username from cte2 
             where DATEDIFF(DAY,a.日期,loginTime)=0 for xml path('')),1,1,''),'无')
from cte1 a      
    
/*
日期         人数          登入人
---------- ----------- --------------
2012-12-23 2           李四,张三
2012-12-24 0           无
2012-12-25 1           张三