27,579
社区成员
发帖
与我相关
我的任务
分享
create table card_log (id int, cardid varchar(10), cardtime datetime)
--------------------------------
insert into card_log select 1, '10001', '2009-8-10 10:00:20'
union all select 2, '10001', '2009-8-10 10:00:20'
union all select 3, '10002', '2009-8-10 10:00:20'
union all select 3, '10002', '2009-8-12 10:00:20'
union all select 4, '10003', '2009-8-12 10:00:20'
create table cards (id int, cardid varchar(10), department varchar(10))
----------------------------------
insert into cards select 1, '10001', 'A01'
union all select 2, '10001' , 'A01'
union all select 3, '10002', 'A01'
union all select 4, '10003', 'A02'
create table ( id int , department varchar(10), departmentname varchar(10))
------------------------------
insert into department select 1, 'A01' , '开发部'
union all select 2, 'A01' , '开发部'
union all select 3, 'A01' , '开发部'
union all select 4, 'A02' , '行政部'
select departmentname,人数=count(distinct c.cardid) from department a
left join cards b on a.department=b.department
left join card_log c on c.cardid= b.cardid
group by departmentname
/*
departmentname 人数
-------------- -----------
开发部 2
行政部 1
(2 行受影响)
*/
这样吗?
create table card_log (id int, cardid varchar(10), cardtime datetime)
--------------------------------
insert into card_log select 1, '10001', '2009-8-11 10:00:20'
union all select 2, '10001', '2009-8-10 10:00:20'
union all select 3, '10002', '2009-8-12 10:00:20'
union all select 4, '10003', '2009-8-12 10:00:20'
create table cards (id int, cardid varchar(10), department varchar(10))
----------------------------------
insert into cards select 1, '10001', 'A01'
union all select 2, '10001' , 'A01'
union all select 3, '10002', 'A01'
union all select 4, '10003', 'A02'
create table ( id int , department varchar(10), departmentname varchar(10))
------------------------------
insert into department select 1, 'A01' , '开发部'
union all select 2, 'A01' , '开发部'
union all select 3, 'A01' , '开发部'
union all select 4, 'A02' , '行政部'
;with t as(
select cardid,
count(distinct cardid) as 次数,
convert(varchar(10),cardtime ,120) as 日期
from card_log
where convert(varchar(10),cardtime ,120) between '2009-08-10' and '2009-08-20'
group by cardid, convert(varchar(10),cardtime ,120)
)
select distinct t.* , b.departmentname from T
left join cards a on t.cardid= a.cardid
left join department b on a.department=b.department
/*
cardid 次数 日期 departmentname
---------- ----------- ---------- --------------
10001 1 2009-08-10 开发部
10001 1 2009-08-11 开发部
10002 1 2009-08-12 开发部
10003 1 2009-08-12 行政部
(4 行受影响)
*/
select departmentname,count(c.cardid) as 人数 --要是同一个人算一次就count(distinct c.cardid)
from department a
left join cards b on b.department=a.department
left join card_log c on b.cards=c.cards
where cardtime between '2009-8-10' and '2009-8-20'
group by departmentname