select id ,isnull(count(logtime),o) from tb where gourp by id
create table test31 (id int, logtimes datetime)insert into test31(ID,LogTimes) select 1,'2010-03-12 13:04:41' union all select 2,'2010-04-13 13:04:44' union all select 3,'2010-05-18 13:04:44' union all select 4,'2010-06-18 13:04:44' union all select 5,'2010-07-18 13:04:44' union all select 6,'2010-05-18 13:04:44' union all select 7,'2010-05-18 13:04:44' union all select 8,'2010-05-18 13:04:44' create table #tmp (i int )Declare @i int set @i=1 while (@i<=12) begin insert into #tmp select @i set @i=@i+1 End select a.i as '月',isnull(b.sl,0) as '数量' from #tmp a left join ( select datepart(month,logtimes) as yue,count(*) as 'sl' from test31 group by datepart(month,logtimes) ) b on a.i=b.yue
select month(logtime) as mon,count(month(logtime)) as num from tb group by mon order by mon
select month(LogTimes) as mon,count(month(LogTimes)) as num from tb group by month(LogTimes) order by mon sry 前面有误。
--> 测试数据:[ta] if object_id('[ta]') is not null drop table [ta] go create table [ta]([ID] int,[LogTimes] datetime) insert [ta] select 1,'2010-03-12 13:04:41' union all select 2,'2010-04-13 13:04:44' union all select 3,'2010-05-18 13:04:44' union all select 4,'2010-06-18 13:04:44' union all select 5,'2010-07-18 13:04:44' union all select 6,'2010-05-18 13:04:44' union all select 7,'2010-05-18 13:04:44' union all select 8,'2010-05-18 13:04:44'--------------------------------查询开始------------------------------select ltrim(spt.number)+'月' as [月份],count(month([LogTimes])) as[统计] from [ta] a right join master..spt_values spt on month([LogTimes])= spt.number where spt.type='p' and spt.number between 1 and 12 group by spt.number /*月份 统计 -------------- ----------- 1月 0 2月 0 3月 1 4月 1 5月 4 6月 1 7月 1 8月 0 9月 0 10月 0 11月 0 12月 0 警告: 聚合或其他 SET 操作消除了空值。(12 行受影响) */
select ltrim(spt.number)+'月' as [月份],count(month([LogTimes])) as[统计] from [ta] a right join master..spt_values spt on month([LogTimes])= spt.number where spt.type='p' and spt.number between 1 and 12 group by spt.number 感谢楼上的朋友,可以指点下吗 有点不知然,当然你的语句是正确的
select ltrim(spt.number)+'月' as [月份],count(month([LogTimes])) as[统计] from [ta] a right join master..spt_values spt on month([LogTimes])= spt.number where spt.type='p' and spt.number between 1 and 12 group by spt.number
哈哈,我给一条新的思路吧:SELECT substring(substring(cast(logtimes as varchar(20)),1,7),6,7),count(substring(cast(logtimes as varchar(20)),1,7)) FROM TABLE GROUP BY substring(cast(logtimes as varchar(20)),1,7)OK了。好处我不多说~
select CAST(month(LogTimes) as varchar(20))+'月' as [月份],count(month(LogTimes)) as [数量] from tb group by month(LogTimes) order by 月份
使有master..spt_values 为的是得到1--12的序列 然后统计关联
select convert(varchar(2),a.fmonth)+'月',isnull(b.fnum,0) (select 1 as fmonth union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12) a left join (select month(logtimes) fmonth,count(*) fnum from 表 group by month(logtimes)) b on a.fmonth=b.fmonth
spt_vales不好吧,依靠ms内部表,ms什么时候将1,或2行删除也不用通知你!
楼主首先要考虑的问题就是按月分组。然后 再。 select ltrim(spt.number)+'月' as [月份],count(month([LogTimes])) as[统计] from [ta] a right join master..spt_values spt on month([LogTimes])= spt.number where spt.type='p' and spt.number between 1 and 12 group by spt.number
(id int,
logtimes datetime)insert into test31(ID,LogTimes)
select 1,'2010-03-12 13:04:41' union all
select 2,'2010-04-13 13:04:44' union all
select 3,'2010-05-18 13:04:44' union all
select 4,'2010-06-18 13:04:44' union all
select 5,'2010-07-18 13:04:44' union all
select 6,'2010-05-18 13:04:44' union all
select 7,'2010-05-18 13:04:44' union all
select 8,'2010-05-18 13:04:44' create table #tmp
(i int )Declare @i int
set @i=1
while (@i<=12)
begin
insert into #tmp
select @i
set @i=@i+1
End
select a.i as '月',isnull(b.sl,0) as '数量' from #tmp a left join (
select datepart(month,logtimes) as yue,count(*) as 'sl' from test31 group by datepart(month,logtimes)
) b on a.i=b.yue
select month(LogTimes) as mon,count(month(LogTimes)) as num from tb group by month(LogTimes) order by mon
sry 前面有误。
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([ID] int,[LogTimes] datetime)
insert [ta]
select 1,'2010-03-12 13:04:41' union all
select 2,'2010-04-13 13:04:44' union all
select 3,'2010-05-18 13:04:44' union all
select 4,'2010-06-18 13:04:44' union all
select 5,'2010-07-18 13:04:44' union all
select 6,'2010-05-18 13:04:44' union all
select 7,'2010-05-18 13:04:44' union all
select 8,'2010-05-18 13:04:44'--------------------------------查询开始------------------------------select ltrim(spt.number)+'月' as [月份],count(month([LogTimes])) as[统计] from [ta] a right join master..spt_values spt
on month([LogTimes])= spt.number
where spt.type='p'
and spt.number between 1 and 12
group by spt.number
/*月份 统计
-------------- -----------
1月 0
2月 0
3月 1
4月 1
5月 4
6月 1
7月 1
8月 0
9月 0
10月 0
11月 0
12月 0
警告: 聚合或其他 SET 操作消除了空值。(12 行受影响)
*/
select ltrim(spt.number)+'月' as [月份],count(month([LogTimes])) as[统计] from [ta] a right join master..spt_values spt
on month([LogTimes])= spt.number
where spt.type='p'
and spt.number between 1 and 12
group by spt.number
感谢楼上的朋友,可以指点下吗 有点不知然,当然你的语句是正确的
on month([LogTimes])= spt.number
where spt.type='p'
and spt.number between 1 and 12
group by spt.number
FROM TABLE
GROUP BY substring(cast(logtimes as varchar(20)),1,7)OK了。好处我不多说~
然后统计关联
(select 1 as fmonth
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10
union select 11
union select 12) a left join
(select month(logtimes) fmonth,count(*) fnum from 表 group by month(logtimes)) b
on a.fmonth=b.fmonth
select ltrim(spt.number)+'月' as [月份],count(month([LogTimes])) as[统计] from [ta] a right join master..spt_values spt
on month([LogTimes])= spt.number
where spt.type='p'
and spt.number between 1 and 12
group by spt.number