刷卡表 a:a_id checkdate checktime
-----------------------------------
1 2008-02-01 08:00:00
1 2008-02-01 17:00:00
1 2008-02-03 08:00:00
1 2008-02-03 13:00:00
1 2008-02-03 17:00:00
1 2008-02-04 17:00:00
2 2008-02-11 08:00:00
2 2008-02-11 17:00:00
2 2008-02-13 08:00:00
3 2008-02-23 13:00:00
4 2008-02-03 17:00:00
4 2008-02-04 17:00:00
a_id唯一,现在要得出每个人这个月有多少天打卡的统计表
如: a_id=2这个人,刷卡记录表有3条记录,但是只有11号和13号两天。
那么得出的结果要是这样的 :a_id 天数
-------------------------
2 2
请指教!!!!!!!
谢谢
-----------------------------------
1 2008-02-01 08:00:00
1 2008-02-01 17:00:00
1 2008-02-03 08:00:00
1 2008-02-03 13:00:00
1 2008-02-03 17:00:00
1 2008-02-04 17:00:00
2 2008-02-11 08:00:00
2 2008-02-11 17:00:00
2 2008-02-13 08:00:00
3 2008-02-23 13:00:00
4 2008-02-03 17:00:00
4 2008-02-04 17:00:00
a_id唯一,现在要得出每个人这个月有多少天打卡的统计表
如: a_id=2这个人,刷卡记录表有3条记录,但是只有11号和13号两天。
那么得出的结果要是这样的 :a_id 天数
-------------------------
2 2
请指教!!!!!!!
谢谢
from (select a_id,checkdate from a group by a_id,checkdate) t
group by a_id
group by a_id
insert into A values(1, '2008-02-01', '08:00:00')
insert into A values(1, '2008-02-01', '17:00:00')
insert into A values(1, '2008-02-03', '08:00:00')
insert into A values(1, '2008-02-03', '13:00:00')
insert into A values(1, '2008-02-03', '17:00:00')
insert into A values(1, '2008-02-04', '17:00:00')
insert into A values(2, '2008-02-11', '08:00:00')
insert into A values(2, '2008-02-11', '17:00:00')
insert into A values(2, '2008-02-13', '08:00:00')
insert into A values(3, '2008-02-23', '13:00:00')
insert into A values(4, '2008-02-03', '17:00:00')
insert into A values(4, '2008-02-04', '17:00:00')
goselect a_id , count(distinct checkdate) 天数 from A group by a_id order by a_id
/*
a_id 天数
----------- -----------
1 3
2 2
3 1
4 2(所影响的行数为 4 行)
*/select a_id , count(*) 天数 from (select distinct a_id , checkdate from A) t group by a_id order by a_id
/*
a_id 天数
----------- -----------
1 3
2 2
3 1
4 2(所影响的行数为 4 行)
*/drop table A
在这里,distinct根据查询所有列找唯一.
from (select a_id,checkdate from a group by a_id,checkdate) t
group by a_id