--表p:
id perid clsid depid adddate
1 1 1 2 2008-1-1 09:20
2 1 1 2 2008-1-1 10:21
3 1 1 2 2008-1-3 11:22
4 2 1 2 2008-1-1 12:23
5 2 1 2 2008-1-1 13:24
6 2 1 2 2008-1-2 14:25
7 3 1 2 2008-1-1 15:26
8 4 1 2 2008-1-1 16:27
9 4 1 2 2008-1-2 17:28--得到表s:
adddate depid clsid persum
2008-1-1 2 1 4 --(同一天一个人的多条只记1)
2008-1-2 2 1 2
2008-1-3 2 1 1
id perid clsid depid adddate
1 1 1 2 2008-1-1 09:20
2 1 1 2 2008-1-1 10:21
3 1 1 2 2008-1-3 11:22
4 2 1 2 2008-1-1 12:23
5 2 1 2 2008-1-1 13:24
6 2 1 2 2008-1-2 14:25
7 3 1 2 2008-1-1 15:26
8 4 1 2 2008-1-1 16:27
9 4 1 2 2008-1-2 17:28--得到表s:
adddate depid clsid persum
2008-1-1 2 1 4 --(同一天一个人的多条只记1)
2008-1-2 2 1 2
2008-1-3 2 1 1
convert(varchar(10),adddate,120),depid,clsid
count(distinct persum) as persum
from
tb
group by
convert(varchar(10),adddate,120),depid,clsid
CONVERT(VARCHAR(10),ADDDATE,120)AS ADDDATE,
DEPID,CLSID,
COUNT(DISTINCT PERID)AS PERSUM
FROM TB
GROUP BY CONVERT(VARCHAR(10),ADDDATE,120)AS ADDDATE,DEPID,CLSID,
(
select distinct convert(varchar(10),adddate,120) adddate, depid , clsid from p
) t
group by adddate, depid , clsid
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-16 17:26:03
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[perid] int,[clsid] int,[depid] int,[adddate] datetime)
insert [tb]
select 1,1,1,2,'2008-1-1 09:20' union all
select 2,1,1,2,'2008-1-1 10:21' union all
select 3,1,1,2,'2008-1-3 11:22' union all
select 4,2,1,2,'2008-1-1 12:23' union all
select 5,2,1,2,'2008-1-1 13:24' union all
select 6,2,1,2,'2008-1-2 14:25' union all
select 7,3,1,2,'2008-1-1 15:26' union all
select 8,4,1,2,'2008-1-1 16:27' union all
select 9,4,1,2,'2008-1-2 17:28'
--------------开始查询--------------------------
select
convert(varchar(10),adddate,120),depid,clsid,
count(distinct perid) as persum
from
tb
group by
convert(varchar(10),adddate,120),depid,clsid
----------------结果----------------------------
/* depid clsid persum
---------- ----------- ----------- -----------
2008-01-01 2 1 4
2008-01-02 2 1 2
2008-01-03 2 1 1(3 行受影响)*/
--> 测试时间:2009-12-16 17:15:56--> 我的淘宝:《戒色坊》http://shop36766744.taobao.com/if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[perid] int,[clsid] int,[depid] int,[adddate] datetime)
insert [TB]
select 1,1,1,2,'2008-1-1 09:20' union all
select 2,1,1,2,'2008-1-1 10:21' union all
select 3,1,1,2,'2008-1-3 11:22' union all
select 4,2,1,2,'2008-1-1 12:23' union all
select 5,2,1,2,'2008-1-1 13:24' union all
select 6,2,1,2,'2008-1-2 14:25' union all
select 7,3,1,2,'2008-1-1 15:26' union all
select 8,4,1,2,'2008-1-1 16:27' union all
select 9,4,1,2,'2008-1-2 17:28'
select adddate=convert(varchar(10),adddate,120),depid,clsid,persum=count(distinct perid) from [TB]
group by convert(varchar(10),adddate,120),depid,clsid/*
adddate depid clsid persum
---------- ----------- ----------- -----------
2008-01-01 2 1 4
2008-01-02 2 1 2
2008-01-03 2 1 1(所影响的行数为 3 行)
*/drop table [TB]
insert [p]
select 1,1,1,2,'2008-1-1 09:20' union all
select 2,1,1,2,'2008-1-1 10:21' union all
select 3,1,1,2,'2008-1-3 11:22' union all
select 4,2,1,2,'2008-1-1 12:23' union all
select 5,2,1,2,'2008-1-1 13:24' union all
select 6,2,1,2,'2008-1-2 14:25' union all
select 7,3,1,2,'2008-1-1 15:26' union all
select 8,4,1,2,'2008-1-1 16:27' select convert(varchar(10),adddate,120) adddate, depid , clsid , persum = count(distinct perid) from p
group by convert(varchar(10),adddate,120), depid , clsid drop table p/*
adddate depid clsid persum
---------- ----------- ----------- -----------
2008-01-01 2 1 4
2008-01-02 2 1 1
2008-01-03 2 1 1(所影响的行数为 3 行)
*/
insert [p]
select 1,1,1,2,'2008-1-1 09:20' union all
select 2,1,1,2,'2008-1-1 10:21' union all
select 3,1,1,2,'2008-1-3 11:22' union all
select 4,2,1,2,'2008-1-1 12:23' union all
select 5,2,1,2,'2008-1-1 13:24' union all
select 6,2,1,2,'2008-1-2 14:25' union all
select 7,3,1,2,'2008-1-1 15:26' union all
select 8,4,1,2,'2008-1-1 16:27' union all
select 9,4,1,2,'2008-1-2 17:28'select convert(varchar(10),adddate,120) adddate, depid , clsid , persum = count(distinct perid) from p
group by convert(varchar(10),adddate,120), depid , clsid drop table p/*
adddate depid clsid persum
---------- ----------- ----------- -----------
2008-01-01 2 1 4
2008-01-02 2 1 2
2008-01-03 2 1 1(所影响的行数为 3 行)
*/
如果时间是限定在2008-1-1到2008-1-4,那么还要显示:
adddate depid clsid persum
2008-1-4 2 1 0
select convert(varchar(10),adddate,120) adddate, depid , clsid , persum = count(distinct perid) from p where adddate between '2008-01-01' and '2008-01-04'
group by convert(varchar(10),adddate,120), depid , clsid
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[perid] int,[clsid] int,[depid] int,[adddate] datetime)
insert [tb]
select 1,1,1,2,'2008-1-1 09:20' union all
select 2,1,1,2,'2008-1-1 10:21' union all
select 3,1,1,2,'2008-1-3 11:22' union all
select 4,2,1,2,'2008-1-1 12:23' union all
select 5,2,1,2,'2008-1-1 13:24' union all
select 6,2,1,2,'2008-1-2 14:25' union all
select 7,3,1,2,'2008-1-1 15:26' union all
select 8,4,1,2,'2008-1-1 16:27' union all
select 9,4,1,2,'2008-1-2 17:28'declare @begdate varchar(10),@enddate varchar(10)select @begdate = '2008-01-01',@enddate = '2008-01-04'select t.[adddate],
t.[depid],t.[clsid],count(distinct r.[perid]) as persum
from [tb] r right join
(select convert(varchar(10),dateadd(day,number,@begdate),120) as [adddate],
2 as [depid],1 as [clsid]
from master..spt_values
where type = 'P' and convert(varchar(10),dateadd(day,number,@begdate),120) <= @enddate) t
on convert(varchar(10),r.[adddate],120) = t.[adddate]
group by t.[adddate],t.[depid],t.[clsid]
---------------------
2008-01-01 2 1 4
2008-01-02 2 1 2
2008-01-03 2 1 1
2008-01-04 2 1 0
select t.[adddate],
t.[depid],t.[clsid],count(distinct r.[perid]) as persum
from [tb] r right join
(select convert(varchar(10),dateadd(day,number,@begdate),120) as [adddate],
h.[depid],h.[clsid]
from master..spt_values ,[tb] h
where type = 'P' and convert(varchar(10),dateadd(day,number,@begdate),120) <= @enddate) t
on convert(varchar(10),r.[adddate],120) = t.[adddate]
group by t.[adddate],t.[depid],t.[clsid]
--------------------------------
2008-01-01 2 1 4
2008-01-02 2 1 2
2008-01-03 2 1 1
2008-01-04 2 1 0