有一个登录记录表ID date
1 2007-08-10
1 2007-09-11
2 2007-09-12
1 2007-12-1
2 2008-1-1我想显示07年十月份的登录次数
要显示为
Id count
1 0
2 0我用select Id,count(Id) from table1 where month(date)=10 group by Id;是不显示where条件中没有的数据的,有什么办法可以显示上面的表?(显示的不仅要某个月的登录,有时还有显示近一周或近一个月的登录次数,mysql数据库)
1 2007-08-10
1 2007-09-11
2 2007-09-12
1 2007-12-1
2 2008-1-1我想显示07年十月份的登录次数
要显示为
Id count
1 0
2 0我用select Id,count(Id) from table1 where month(date)=10 group by Id;是不显示where条件中没有的数据的,有什么办法可以显示上面的表?(显示的不仅要某个月的登录,有时还有显示近一周或近一个月的登录次数,mysql数据库)
再用Left Join去连接你上面这个表
加个where去限制条件就行.
from (
select distinct id
from logs) as a
left join (
select id, count(1) as cnt
from logs
where date between @bgnDate and @endDate
group by id) as b on a.id=b.id;
from (
select distinct id
from logs) as a
left join (
select id, count(1) as cnt
from logs
where date between @bgnDate and @endDate
group by id) as b on a.id=b.id;