【 最后80分 】：sql按月统计访问次数

Question

if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb(id varchar(10),date datetime,type int)
insert into tb(id,date,type) values('v1' ,'2007-03-29 19:41:00',1)
insert into tb(id,date,type) values('v2' ,'2007-03-12 8:40:12' ,1)
insert into tb(id,date,type) values('v3' ,'2007-03-13 9:41:33' ,2)
insert into tb(id,date,type) values('v4' ,'2007-02-13 9:41:25' ,2)
insert into tb(id,date,type) values('v5' ,'2007-01-12 9:41:00' ,1)
insert into tb(id,date,type) values('v6' ,'2006-12-12 9:41:00' ,3)
insert into tb(id,date,type) values('v7' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v8' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v9' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v10','2006-12-13 9:41:00' ,1)
godeclare @dt1 as varchar(7)
declare @dt2 as varchar(7)
declare @dt3 as varchar(7)
set @dt1 = convert(varchar(7),getdate(),120)
set @dt2 = convert(varchar(7),dateadd(month,-1,getdate()),120)
set @dt3 = convert(varchar(7),dateadd(month,-2,getdate()),120)
declare @sql as varchar(8000)
set @sql = '
select type , 
       sum(case when convert(varchar(7),date,120) = ''' + @dt1 + ''' then 1 else 0 end) as [' + @dt1 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt2 + ''' then 1 else 0 end) as [' + @dt2 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt3 + ''' then 1 else 0 end) as [' + @dt3 + ']
from tb
group by type'
exec(@sql)drop table tb
/*
type        2007-03     2007-02     2007-01     
----------- ----------- ----------- ----------- 
1           2           0           1
2           1           1           0
3           0           0           0
*/

Answer 1

if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb(id varchar(10),date datetime,type int)
insert into tb(id,date,type) values('v1' ,'2007-03-29 19:41:00',1)
insert into tb(id,date,type) values('v2' ,'2007-03-12 8:40:12' ,1)
insert into tb(id,date,type) values('v3' ,'2007-03-13 9:41:33' ,2)
insert into tb(id,date,type) values('v4' ,'2007-02-13 9:41:25' ,2)
insert into tb(id,date,type) values('v5' ,'2007-01-12 9:41:00' ,1)
insert into tb(id,date,type) values('v6' ,'2006-12-12 9:41:00' ,3)
insert into tb(id,date,type) values('v7' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v8' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v9' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v10','2006-12-13 9:41:00' ,1)
godeclare @dt1 as varchar(7)
declare @dt2 as varchar(7)
declare @dt3 as varchar(7)
set @dt1 = convert(varchar(7),getdate(),120)
set @dt2 = convert(varchar(7),dateadd(month,-1,getdate()),120)
set @dt3 = convert(varchar(7),dateadd(month,-2,getdate()),120)
declare @sql as varchar(8000)
set @sql = '
select type , 
       sum(case when convert(varchar(7),date,120) = ''' + @dt1 + ''' then 1 else 0 end) as [' + @dt1 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt2 + ''' then 1 else 0 end) as [' + @dt2 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt3 + ''' then 1 else 0 end) as [' + @dt3 + ']
into test
from tb
group by type'
exec(@sql)set @sql = 'select m.* ,n.最大值 , n.最小值 from test m , (select type , max(value) as 最大值 , min(value) as 最小值 from
(select type , [' + @dt1 + '] value from test ' +
'union all ' +
'select type , [' + @dt2 + '] value from test ' +
'union all ' +
'select type , [' + @dt3 + '] value from test) t ' +
'group by type) n ' +
'where m.type = n.type'exec(@sql)drop table tb , test/*
type        2007-03     2007-02     2007-01     最大值      最小值         
----------- ----------- ----------- ----------- ----------- ----------- 
1           2           0           1           2           0
2           1           1           0           1           0
3           0           0           0           0           0
*/

Answer 2

create table tb(id varchar(10),date datetime,[type] int)
insert into tb(id,date,[type]) values('v1' ,'2007-03-29 19:41:00',1)
insert into tb(id,date,[type]) values('v2' ,'2007-03-12 8:40:12' ,1)
insert into tb(id,date,[type]) values('v3' ,'2007-03-13 9:41:33' ,2)
insert into tb(id,date,[type]) values('v4' ,'2007-02-13 9:41:25' ,2)
insert into tb(id,date,[type]) values('v5' ,'2007-01-12 9:41:00' ,1)
insert into tb(id,date,[type]) values('v6' ,'2006-12-12 9:41:00' ,3)
insert into tb(id,date,[type]) values('v7' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v8' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v9' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v10','2006-12-13 9:41:00' ,1)--(select date=convert(varchar(6),date,112) from tb)
declare @s varchar(4000)
set @s=''
select @s=@s+','+quotename(convert(varchar(7),date,120))+'=sum(case convert(varchar(7),date,120) when '
+ quotename(convert(varchar(7),date,120),'''')+ ' then 1 else 0 end)'
from tb group by convert(varchar(7),date,120)
set @s='select [type]'+@s+' into # from tb group by [type]
select * ,[max]=(select max(a) from (select [2006-12] as a 
union all select [2007-01]   
union all select [2007-02]  
union all select [2007-03])ta)
,[min]=(select min(a) from (select [2006-12] as a 
union all select [2007-01]   
union all select [2007-02]  
union all select [2007-03])ta)
from #'
exec(@s)(3 行受影响)
type        2006-12     2007-01     2007-02     2007-03     max         min
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           4           1           0           2           4           0
2           0           0           1           1           1           0
3           1           0           0           0           1           0(3 行受影响)

Answer 3

create table tb(id varchar(10),date datetime,[type] int)
insert into tb(id,date,[type]) values('v1' ,'2007-03-29 19:41:00',1)
insert into tb(id,date,[type]) values('v2' ,'2007-03-12 8:40:12' ,1)
insert into tb(id,date,[type]) values('v3' ,'2007-03-13 9:41:33' ,2)
insert into tb(id,date,[type]) values('v4' ,'2007-02-13 9:41:25' ,2)
insert into tb(id,date,[type]) values('v5' ,'2007-01-12 9:41:00' ,1)
insert into tb(id,date,[type]) values('v6' ,'2006-12-12 9:41:00' ,3)
insert into tb(id,date,[type]) values('v7' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v8' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v9' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v10','2006-12-13 9:41:00' ,1)
--全动态统计(需要两个变量)
declare @s varchar(4000),@s2 varchar(4000)
select @s=''
select @s=@s+','+quotename(convert(varchar(7),date,120))+'=sum(case convert(varchar(7),date,120) when '
+ quotename(convert(varchar(7),date,120),'''')+ ' then 1 else 0 end)'
from tb group by convert(varchar(7),date,120)select @s2=isnull(@s2+' union all select ','select ')+quotename(convert(varchar(7),date,120))+'as a'
from tb group by convert(varchar(7),date,120)
--print @s2
set @s='select [type]'+@s+' into # from tb group by [type]
select * ,[max]=(select max(a) from ('+@s2+')ta),
[min]=(select min(a) from ('+@s2+')ta)'+'from #'
--print @s
exec(@s)
(3 行受影响)
type        2006-12     2007-01     2007-02     2007-03     max         min
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           4           1           0           2           4           0
2           0           0           1           1           1           0
3           1           0           0           0           1           0(3 行受影响)

Answer 4

roy_88(中国风_燃烧你的激情！！！) 
有问题啊
Column 'tb.date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 5

if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb(id varchar(10),date datetime,type int)
insert into tb(id,date,type) values('v1' ,'2007-03-29 19:41:00',1)
insert into tb(id,date,type) values('v2' ,'2007-03-12 8:40:12' ,1)
insert into tb(id,date,type) values('v3' ,'2007-03-13 9:41:33' ,2)
insert into tb(id,date,type) values('v4' ,'2007-02-13 9:41:25' ,2)
insert into tb(id,date,type) values('v5' ,'2007-01-12 9:41:00' ,1)
insert into tb(id,date,type) values('v6' ,'2006-12-12 9:41:00' ,3)
insert into tb(id,date,type) values('v7' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v8' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v9' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v10','2006-12-13 9:41:00' ,1)
godeclare @dt1 as varchar(7)
declare @dt2 as varchar(7)
declare @dt3 as varchar(7)
declare @dt4 as varchar(7)
set @dt1 = convert(varchar(7),getdate(),120)
set @dt2 = convert(varchar(7),dateadd(month,-1,getdate()),120)
set @dt3 = convert(varchar(7),dateadd(month,-2,getdate()),120)
set @dt4 = convert(varchar(7),dateadd(month,-3,getdate()),120)
declare @sql as varchar(8000)
set @sql = '
select type , 
       sum(case when convert(varchar(7),date,120) = ''' + @dt1 + ''' then 1 else 0 end) as [' + @dt1 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt2 + ''' then 1 else 0 end) as [' + @dt2 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt3 + ''' then 1 else 0 end) as [' + @dt3 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt4 + ''' then 1 else 0 end) as [' + @dt4 + ']
into test
from tb
group by type'
exec(@sql)set @sql = 'select m.* ,n.最大值 , n.最小值 from test m , (select type , max(value) as 最大值 , min(value) as 最小值 from
(select type , [' + @dt1 + '] value from test ' +
'union all ' +
'select type , [' + @dt2 + '] value from test ' +
'union all ' +
'select type , [' + @dt3 + '] value from test ' +
'union all ' +
'select type , [' + @dt4 + '] value from test) t ' +
'group by type) n ' +
'where m.type = n.type'exec(@sql)drop table tb , test/*
type    2007-03     2007-02     2007-01     2006-12     最大值      最小值         
------- ----------- ----------- ----------- ----------- ----------- ----------- 
1       2           0           1           4           4           0
2       1           1           0           0           1           0
3       0           0           0           1           1           0
*/

Answer 6

dawugui(潇洒老乌龟) 方法可以，但给我创建了一个test表 还有请问要是统计天的，条件该怎么该呢？我试着将变量改为
declare @this as varchar(10)
declare @last as varchar(10)
declare @lastlast as varchar(10)
set @this = convert(varchar(10),getdate(),120)
set @last = convert(varchar(10),dateadd(day,-1,getdate()),120)
set @lastlast = convert(varchar(10),dateadd(day,-2,getdate()),120)这里改为
 sum(case when convert(varchar(10),date,120) = ''' + @dt1 + ''' then 1 else 0 end) as [' + @dt1 + '],
       sum(case when convert(varchar(10),date,120) = ''' + @dt2 + ''' then 1 else 0 end) as [' + @dt2 + '],
       sum(case when convert(varchar(10),date,120) = ''' + @dt3 + ''' then 1 else 0 end) as [' + @dt3 + '],
       sum(case when convert(varchar(10),date,120) = ''' + @dt4 + ''' then 1 else 0 end) as [' + @dt4 + ']为什么统计的有错了，请指教

Answer 7

取最大值、最小值 的时候能不能取到对应的具体日期呢？比如查月的时候 最大值的月份取出来  
type    2007-03     2007-02     2007-01     2006-12     最大值    最大值月        
------- ----------- ----------- ----------- ----------- ----------- ----------- 
1       2           0           1           4           4           2006-12 
2       1           1           0           0           1           2007-03 
..............

Answer 8

--大乌龟不在，我来改一下大乌龟的
if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb(id varchar(10),date datetime,type int)
insert into tb(id,date,type) values('v1' ,'2007-03-29 19:41:00',1)
insert into tb(id,date,type) values('v2' ,'2007-03-12 8:40:12' ,1)
insert into tb(id,date,type) values('v3' ,'2007-03-13 9:41:33' ,2)
insert into tb(id,date,type) values('v4' ,'2007-02-13 9:41:25' ,2)
insert into tb(id,date,type) values('v5' ,'2007-01-12 9:41:00' ,1)
insert into tb(id,date,type) values('v6' ,'2006-12-12 9:41:00' ,3)
insert into tb(id,date,type) values('v7' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v8' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v9' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,type) values('v10','2006-12-13 9:41:00' ,1)
godeclare @dt1 as varchar(7)
declare @dt2 as varchar(7)
declare @dt3 as varchar(7)
set @dt1 = convert(varchar(7),getdate(),120)
set @dt2 = convert(varchar(7),dateadd(month,-1,getdate()),120)
set @dt3 = convert(varchar(7),dateadd(month,-2,getdate()),120)
declare @sql as varchar(8000)
set @sql = '
select type , 
       sum(case when convert(varchar(7),date,120) = ''' + @dt1 + ''' then 1 else 0 end) as [' + @dt1 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt2 + ''' then 1 else 0 end) as [' + @dt2 + '],
       sum(case when convert(varchar(7),date,120) = ''' + @dt3 + ''' then 1 else 0 end) as [' + @dt3 + ']
into test
from tb
group by type'
exec(@sql)set @sql='select type,ym=cast(year(min(date)) as varchar(10))+''-''+cast(month(min(date)) as varchar(10)),
value=count(*) into test2 from tb group by type,year(date),month(date)'
exec(@sql)set @sql = 'select m.* ,n.最大值 , n.最小值, n.最大值月, n.最小值月 from test m ,
 (select m.type , max(m.value) as 最大值 , min(n.value) as 最小值,最大值月=max(m.ym),最小值月=min(n.ym)
from
(select * from test2 as T
where not exists (select * from test2 where type=T.type and value>T.value))m,
(select * from test2 as T
where not exists (select * from test2 where type=T.type and value<T.value))n
where m.type=n.type
group by m.type) n ' +
'where m.type = n.type'
exec(@sql)drop table tb , test , test2/*
type   2007-03  2007-02  2007-01  最大值  最小值     最大值月 最小值月  
---------------------------------------------------------------------------------
1 2 0 1 4 1 2006-12 2007-1
2 1 1 0 1 1 2007-3 2007-2
3 0 0 0 1 1 2006-12 2006-12
*/

Answer 9

gc_ding(施主,给个妞泡好么) 执行一下，明显不对啊？？type   2007-03  2007-02  2007-01  最大值  最小值     最大值月 最小值月  
---------------------------------------------------------------------------------
1 2 0 1 4 1 2006-12 2007-1
最小值是2007-02  的0，最小值月也应该是2007-02

Answer 10

不间断月？
type 2007-03  2007-02 2007-01  2006-12 2006-09 最小值      最小值月  
---------------------------------------------------------------------------------
1      2       0      1        12     2      0     2007-02,2006-10,2006-11

Answer 11

roy_88(中国风_燃烧你的激情！！！) 
有问题啊
Column 'tb.date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.?
根据提示是group by 问题,增加一个变量就行了create table tb(id varchar(10),date datetime,[type] int)
insert into tb(id,date,[type]) values('v1' ,'2007-03-29 19:41:00',1)
insert into tb(id,date,[type]) values('v2' ,'2007-03-12 8:40:12' ,1)
insert into tb(id,date,[type]) values('v3' ,'2007-03-13 9:41:33' ,2)
insert into tb(id,date,[type]) values('v4' ,'2007-02-13 9:41:25' ,2)
insert into tb(id,date,[type]) values('v5' ,'2007-01-12 9:41:00' ,1)
insert into tb(id,date,[type]) values('v6' ,'2006-12-12 9:41:00' ,3)
insert into tb(id,date,[type]) values('v7' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v8' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v9' ,'2006-12-13 9:41:00' ,1)
insert into tb(id,date,[type]) values('v10','2006-12-13 9:41:00' ,1)
--全动态统计(需要两个变量)
declare @s varchar(4000),@s2 varchar(4000),@s3 varchar(4000)
select @s=''
select @s=@s+','+quotename(convert(varchar(7),date,120))+'=sum(case convert(varchar(7),date,120) when '
+ quotename(convert(varchar(7),date,120),'''')+ ' then 1 else 0 end)'
from tb group by convert(varchar(7),date,120)
--print @s
select @s2=isnull(@s2+' union all select ','select ')+quotename(convert(varchar(7),date,120))+'as a'
from tb group by convert(varchar(7),date,120)
--print @s2
select @s3=isnull(@s3+',','')+quotename(convert(varchar(7),date,120))
from tb group by convert(varchar(7),date,120)
--如果@s的字符太长时用
exec('select [type]'+@s+' into # from tb group by [type]
select * ,[max]=(select max(a) from ('+@s2+')ta),
[min]=(select min(a) from ('+@s2+')ta)'+'from # group by [type],'+@s3)

Answer 12

roy_88(中国风_燃烧你的激情！！！) 请赐教，这个东西我是真没头绪，理不清思路

Answer 13

Create Table TEST(id Varchar(10), date Datetime, type Int)
Insert Into TEST Select 'v1' ,'2007-03-29 19:41:00',1
Union All Select 'v2' ,'2007-03-12 8:40:12' ,1
Union All Select 'v3' ,'2007-03-13 9:41:33' ,2
Union All Select 'v4' ,'2007-02-13 9:41:25' ,2
Union All Select 'v5' ,'2007-01-12 9:41:00' ,1
Union All Select 'v6' ,'2006-12-12 9:41:00' ,3
Union All Select 'v7' ,'2006-12-13 9:41:00' ,1
Union All Select 'v8' ,'2006-12-13 9:41:00' ,1
Union All Select 'v9' ,'2006-12-13 9:41:00' ,1
Union All Select 'v10','2006-12-13 9:41:00' ,1
GO
Create Procedure SP_TEST
As
Begin
Declare @S Varchar(8000) Select type, Convert(Varchar(7), [date], 120) As 月, Count(*) As 量 Into #T From TEST Group By type, Convert(Varchar(7), [date], 120) Select @S = ' Select type '
Select @S = @S +  ', SUM(Case DateDiff(mm, 月 + ''-01'', GetDate()) When 0 Then 量 Else 0 End) As [' +  Convert(Varchar(7), GetDate(), 120) + ']'
+  ', SUM(Case DateDiff(mm, 月 + ''-01'', GetDate()) When 1 Then 量 Else 0 End) As [' +  Convert(Varchar(7), DateAdd(mm, -1, GetDate()), 120) + ']'
+ ', SUM(Case DateDiff(mm, 月 + ''-01'' , GetDate()) When 2 Then 量 Else 0 End) As [' +  Convert(Varchar(7), DateAdd(mm, -2, GetDate()), 120) + ']'
Select @S = @S + ' , Min(量) As 最小量, (Select TOP 1 月 From #T Where type = A.type Order By 量, 月 Desc) As 最小量月, Max(量) As 最大量, (Select TOP 1 月 From #T Where type = A.type Order By 量 Desc, 月 Desc) As 最大量月'
Select @S = @S + ' From #T A Group By A.type'
Print @S
EXEC(@S)
Drop Table #T
End
GO
EXEC SP_TEST
GO
Drop Table TEST
Drop Procedure SP_TEST
--Result
/*
type 2007-03 2007-02 2007-01 最小量 最小量月 最大量 最大量月
1 2 0 1 1 2007-01 4 2006-12
2 1 1 0 1 2007-03 1 2007-03
3 0 0 0 1 2006-12 1 2006-12
*/