username uploadtime uNum
系统用户 2009-6 11
系统用户 2009-8 1
系统用户 2009-9 1如上,
select*from V_UploadMonth where UploadTime >'2009-5' and UploadTime<'2009-10'
这个语句查不到....?
系统用户 2009-6 11
系统用户 2009-8 1
系统用户 2009-9 1如上,
select*from V_UploadMonth where UploadTime >'2009-5' and UploadTime<'2009-10'
这个语句查不到....?
SELECT dbo.[User].UserName, CONVERT(varchar, YEAR(dbo.Upload.UploadTime)) + '-' + CONVERT(varchar, MONTH(dbo.Upload.UploadTime)) AS UploadTime,
COUNT(*) AS uNum
FROM dbo.Upload INNER JOIN
dbo.[User] ON dbo.[User].ID = dbo.Upload.UploadUserID
WHERE (dbo.[User].RoleID = 1) AND (dbo.Upload.Deleted = 0)
GROUP BY dbo.[User].UserName, CONVERT(varchar, YEAR(dbo.Upload.UploadTime)) + '-' + CONVERT(varchar, MONTH(dbo.Upload.UploadTime))
CONVERT(varchar(7), UploadTime, 23)
要不然你就用datetime类型
select*from V_UploadMonth where UploadTime >'2009-5' and UploadTime <'2009-10'
查询到呢,因为我查询的条件只需要精确到月,也就是YYYY-MM就够了
时间型的这样处理
from V_UploadMonth
where UploadTime between '2009-6' and '2009-9'
select * from V_UploadMonth where UploadTime between '2009-5' and '2009-10'
如果是6月到9月,23楼的就可以,如果是3月到11月,就要这样:
select *
from V_UploadMonth
where (UploadTime between '2009-3' and '2009-9')
or UploadTime in ('2009-10','2009-11')
from V_UploadMonth
where UploadTime in ('2009-6','2009-7','2009-8','2009-9')
declare @t1 table(username varchar(20),uploadtime varchar(20),uNum int)
insert into @t1 values('系统用户','2009-6',11)
insert into @t1 values('系统用户','2009-8',1)
insert into @t1 values('系统用户','2009-9',1)
select * from @t1 where Convert(datetime,UploadTime+'-01') >Convert(datetime,'2009-5'+'-01')
and Convert(datetime,UploadTime+'-01') <Convert(datetime,'2009-10'+'-01' )
UploadTimebetween'2009-3'and'2009-9'改成UploadTimebetween'2009-3'and'2009-12'就不行了
“2009-5”与“2009-10”,前面的“2009-”都是一样的,后面的“5”>“1”,所以找不到
V_UploadMonth这个视图是用来提取User里面的UserName,Upload里面的UploadTime
User表的结构大同小异,Upload的UploadTime类型是datetime
declare @t table(username varchar(20),uploadtime datetime,uNum int)insert into @t select '系统用户','2009-06-29 08:16:18.907',5
union all select '系统用户', '2009-06-29 08:30:08.620', 3
union all select '系统用户', '2009-06-30 12:00:55.917', 3
union all select '系统用户', '2009-08-31 14:35:10.327', 1
union all select '系统用户', '2009-09-02 10:00:16.787', 1select * from @t select min(username) username,cast(YEAR(uploadtime) as varchar(4))+'-'+cast(month(uploadtime) as varchar(2)) as [date],sum(uNum) uNum from @t
where uploadtime>'2009-05-01' and uploadtime<'2009-10-01'
group by cast(YEAR(uploadtime) as varchar(4))+'-'+cast(month(uploadtime) as varchar(2))
declare @t1 table(username varchar(20),uploadtime varchar(20),uNum int)
insert into @t1 values('系统用户','2009-6',11)
insert into @t1 values('系统用户','2009-8',1)
insert into @t1 values('系统用户','2009-9',1) select * from @t1
where Convert(int,REPLACE( uploadtime,'-','')) between
Convert(int,REPLACE( '2009-5','-','')) and
Convert(int,REPLACE( '2009-10','-',''))
系统用户 2009-06-29 08:16:18.907 5
系统用户 2009-06-29 08:30:08.620 3
系统用户 2009-06-30 12:00:55.917 3
系统用户 2009-08-31 14:35:10.327 1
系统用户 2009-09-02 10:00:16.787 1
declare @maxvalue varchar(100)
set @minvalue='2009-5'
set @maxvalue='2009-10'
select*from V_UploadMonth where
cast(uploadtime+'-1' as datetime) between cast(@minvalue+'-1' as datetime) and cast (@maxvalue+'-1' as datetime)
where uploadtime>'2009-05-01' and uploadtime<'2009-10-01'
group by cast(YEAR(uploadtime) as varchar(4))+'-'+cast(month(uploadtime) as varchar(2))group by cast(YEAR(uploadtime) as varchar(4))+'-'+cast(month(uploadtime) as varchar(2))
这句 就是根据 记录的年月分组
insert into @t1 values('系统用户','2009-6',11)
insert into @t1 values('系统用户','2009-8',1)
insert into @t1 values('系统用户','2009-9',1) 那这个是什么意思呢?
Must declare the table variable "@t1".
declare @t1 table(username varchar(20),uploadtime datetime,uNum int)
insert into @t1 values('系统用户' ,'2009-06-29 08:16:18.907', 5 )
insert into @t1 values('系统用户' ,'2009-06-29 08:30:08.620', 3 )
insert into @t1 values('系统用户' ,'2009-06-30 12:00:55.917', 3 )
insert into @t1 values('系统用户' ,'2009-08-31 14:35:10.327', 1 )
insert into @t1 values('系统用户' ,'2009-09-02 10:00:16.787', 1 )
select LEFT(Convert(varchar(20),uploadtime,120),7) as uploadtime,sum(uNum) as uNum from @t1group by LEFT(Convert(varchar(20),uploadtime,120),7)
select min(username) username,
cast(YEAR(uploadtime) as varchar(4))+'-'+cast(month(uploadtime) as varchar(2)) as [date],
sum(uNum) uNum from @t
where uploadtime>'2009-05-01' and uploadtime<'2009-10-01'
group by cast(YEAR(uploadtime) as varchar(4))+'-'+cast(month(uploadtime) as varchar(2))@t 换成你的表名
select Convert(varchar(7),uploadtime,120) as uploadtime,sum(uNum) as uNum from @t1group by Convert(varchar(7),uploadtime,120)或者指定长度为7就行了
SQLSERVER博大精深啊
union all select '系统用户', '2009-06-29 08:30:08.620', 3
union all select '系统用户', '2009-06-30 12:00:55.917', 3
union all select '系统用户', '2009-08-31 14:35:10.327', 1
union all select '系统用户', '2009-09-02 10:00:16.787', 1select * from @a select min(username) username,cast(YEAR(uploadtime) as varchar(4))+'-'+cast(month(uploadtime) as varchar(2)) as [date],sum(uNum) uNum from @t
where uploadtime>'2009-05-01' and uploadtime<'2009-10-01'
group by cast(YEAR(uploadtime) as varchar(4))+'-'+cast(month(uploadtime) as varchar(2))看看这个!