张表叫UserInfo:里面有UserID,CreateTime(用户创建的时间)
还有张表叫User_Profile;里面有UserID,TotalPosts(帖子总数)
现在要统计出一个用户的平均日发帖数,就是用TotalPosts去除以当前时间减去CreateTime 然后把这个平均日发帖数作为一个别名select a.UserID,1.0*TotalPosts/datediff(day,CreateTime,getdate()) as 别名
from UserInfo a,User_Profile b
where a.UserID=b.UserID
还有张表叫User_Profile;里面有UserID,TotalPosts(帖子总数)
现在要统计出一个用户的平均日发帖数,就是用TotalPosts去除以当前时间减去CreateTime 然后把这个平均日发帖数作为一个别名select a.UserID,1.0*TotalPosts/datediff(day,CreateTime,getdate()) as 别名
from UserInfo a,User_Profile b
where a.UserID=b.UserID
from UserInfo a left join User_Profile b on a.UserID = b.UserID
create table UserInfo
(
UserID int,
CreateTime datetime
)create table User_Profile
(
UserID int,
TotalPosts int
)
go
insert into UserInfo values(1,getdate()-18)
insert into User_Profile values(1,1258)
***/
select x.Userid,
1.0 * y.TotalPosts/DATEDIFF ( day , convert(datetime,x.CreateTime,102), getdate()) As 平均日发帖数
from UserInfo x ,User_Profile y
where x.Userid=y.Userid
select a.userid,TotalPosts/datediff(day,CreateTime,GETDATE()) as 平均日发帖数
from UserInfo a,User_Profile b
where a.UserId=b.UserId
group by a.userid,TotalPosts,CreateTime
但是平均日发帖数返回的是整型的,当小于1时,都会返回0,不好区分