数据库有两个别表分别是用户表(user)和用户日志表(userlog)
用户日志表是纪录用户每访问一个页面的url,和ip
结构如下:
user:
(用户id主键)u_ID int
(用户名)u_UserName varchar
(用户密码)u_PassWord varchar userlog
(主键)ul_ID int
(外键)ul_UserID int
(访问的页面url)ul_Url varchar
(访问用户的ip)ul_VisitIP varchar
(访问时间)ul_VisitTime smalldatetime 现在我想用一条查询语句查询用户访问的页面数在2000-3000之间的用户名
怎么写这条sql语句
用户日志表是纪录用户每访问一个页面的url,和ip
结构如下:
user:
(用户id主键)u_ID int
(用户名)u_UserName varchar
(用户密码)u_PassWord varchar userlog
(主键)ul_ID int
(外键)ul_UserID int
(访问的页面url)ul_Url varchar
(访问用户的ip)ul_VisitIP varchar
(访问时间)ul_VisitTime smalldatetime 现在我想用一条查询语句查询用户访问的页面数在2000-3000之间的用户名
怎么写这条sql语句
问题是user 表里面根本就没有记录访问页面数这个字段
select u.u_username from [user] u ,(select ul_UserID from userlog group by ul_id having count(1) between 2000 and 3000) t
where u.u_id =t.ul_UserID
你再试下
u_UserName
from user A
inner join userlog B on A.u_ID = B.ul_UserID
where ul_id between 2000 and 3000
select u_username from [user] where u_id in(select ul_UserID from userlog group by ul_UserID having ul_UserID between 2000 and 3000)
FROM [userlog] AS A INNER JOIN [user] AS B ON A.ul_ID=B.ul_ID
GROUP BY A.u_ID,B.u_UserName
HAVING COUNT(1) BETWEEN 2000 AND 3000