有user和task 2张表想统计一个数据每天不重复的用户登入的次数和具体列表
user表 包括 taskID,UserName。
task表 包括 taskID,loginTime。
数据
user表
username taskID
张三 1
张三 2
张三 3
李四 4task表
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 张三
user表 包括 taskID,UserName。
task表 包括 taskID,loginTime。
数据
user表
username taskID
张三 1
张三 2
张三 3
李四 4task表
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 张三
(
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
日期,人数
-- 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 就不写了
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构造时间的方法在这里
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 张三
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 张三