--没看明白哪里需要遍历,只是为了实现排序功能?create proc spaa @UserID int as select HeadPictureAddress,UserName, Sex,Age,ProvinceArea,StudyLevel,Earn from UserTable INNER JOIN VisitUsers ON UserTable.UserID=VisitUsers.VisiterID ORDER BY VisitUsers.访问时间
create proc spaa @UserID int as /* 问 : 我想按照时间将VisiterID排序,可排序又不能用在子查询里面 答 :对VisiterID排序 不就是对 UserID 排序 吗? */ select HeadPictureAddress,UserName,Sex,Age,ProvinceArea,StudyLevel,Earn from UserTable where UserID in(select VisiterID from VisitUsers where HostID=@UserID ) order by UserID /* 问:然后就是where UserID in(select VisiterID from VisitUsers where HostID=@UserID ) 用UserID in...可以遍历VisiterID的所有查询出来的结果么?? 答:可以 */
如果(select VisiterID from VisitUsers where HostID=@UserID ) 的VisiterID没有重复值,可以如下简单变换create proc spaa @UserID int as select a.HeadPictureAddress,a.UserName,a.Sex,a.Age,a.ProvinceArea,a.StudyLevel,a.Earn from UserTable a,VisitUsers b where a.UserID =b.VisiterID and b.HostID=@UserID order by b.时间字段
你想遍历做什么? SQL是set-based命令, 也就是说, SQL优点在于一次处理多个记录. 而一条一条的处理是SQL的弱项. 实在万不得已才用到SQL的逐条处理的命令: CURSOR你的存储过程可以写成:create proc spaa @UserID int as Begin select A.HeadPictureAddress,A.UserName,A.Sex,A.Age,A.ProvinceArea,A.StudyLevel,A.Earn from UserTable A INNER JOIN VisitUsers B ON A.UserID = B.VisiterID WHERE B.HostID = @UserID END GO
如果(select VisiterID from VisitUsers where HostID=@UserID ) 的VisiterID有重复值,则需要找出 (select VisiterID,COL from VisitUsers where HostID=@UserID )不重复的COL字段,如下查询create proc spaa @UserID int as select a.HeadPictureAddress,a.UserName,a.Sex,a.Age,a.ProvinceArea,a.StudyLevel,a.Earn from UserTable a,VisitUsers b where a.UserID =b.VisiterID and b.HostID=@UserID and not exists ( select 1 from VisitUsers where VisiterID=b.VisiterID and COL>b.COL ) order by b.时间字段
create proc spaa @UserID int as select HeadPictureAddress,UserName,Sex,Age,ProvinceArea,StudyLevel,Earn from UserTable a,VisitUsers b where a.UserID=b.VisiterID and b.HostID=@UserID order by 时间字段
估计会有重复值,而且应该以最近的一次访问时间为排序依据create proc spaa @UserID int as select a.HeadPictureAddress,a.UserName,a.Sex,a.Age,a.ProvinceArea,a.StudyLevel,a.Earn from UserTable a join (select VisiterID,LastVisitTime=max(VisitTime) from VisitUsers where HostID=@UserID group by VisiterID) b on a.UserID =b.VisiterID order by b.LastVisitTime
对VisiterID排序 不是对 UserID 排序 因为VisiterUser表中才有访问时间啊,所以按照VisiterUser中的访问时间排序,,而且我还要遍历select VisiterID from VisitUsers where HostID=@UserID 查询出来的结果,请大虾再指点一下
select 1 from VisitUsers where VisiterID=b.VisiterID and COL>b.COL 的意思具体是什么呢?COL字段表示什么呢?还有 VisiterID=b.VisiterID是什么意思呢? 然后您的存储过程中where a.UserID =b.VisiterID and b.HostID=@UserID 能遍历VisitUsers表中的符合要求的VisiterID的值么??请大虾耐心讲解, 小弟深表谢意!
@UserID int
as
/*
问 : 我想按照时间将VisiterID排序,可排序又不能用在子查询里面
答 :对VisiterID排序 不就是对 UserID 排序 吗?
*/
select HeadPictureAddress,UserName,Sex,Age,ProvinceArea,StudyLevel,Earn
from UserTable where UserID in(select VisiterID from VisitUsers where HostID=@UserID )
order by UserID /* 问:然后就是where UserID in(select VisiterID from VisitUsers where HostID=@UserID )
用UserID in...可以遍历VisiterID的所有查询出来的结果么??
答:可以
*/
的VisiterID没有重复值,可以如下简单变换create proc spaa
@UserID int
as
select a.HeadPictureAddress,a.UserName,a.Sex,a.Age,a.ProvinceArea,a.StudyLevel,a.Earn
from UserTable a,VisitUsers b
where a.UserID =b.VisiterID
and b.HostID=@UserID
order by b.时间字段
@UserID int
as
Begin
select A.HeadPictureAddress,A.UserName,A.Sex,A.Age,A.ProvinceArea,A.StudyLevel,A.Earn
from UserTable A
INNER JOIN VisitUsers B
ON A.UserID = B.VisiterID
WHERE B.HostID = @UserID
END
GO
的VisiterID有重复值,则需要找出 (select VisiterID,COL from VisitUsers where HostID=@UserID )不重复的COL字段,如下查询create proc spaa
@UserID int
as
select a.HeadPictureAddress,a.UserName,a.Sex,a.Age,a.ProvinceArea,a.StudyLevel,a.Earn
from UserTable a,VisitUsers b
where a.UserID =b.VisiterID
and b.HostID=@UserID
and not exists (
select 1 from VisitUsers
where VisiterID=b.VisiterID
and COL>b.COL
)
order by b.时间字段
create proc spaa
@UserID int
as
select HeadPictureAddress,UserName,Sex,Age,ProvinceArea,StudyLevel,Earn from UserTable a,VisitUsers b
where a.UserID=b.VisiterID and b.HostID=@UserID
order by 时间字段
@UserID int
as
select a.HeadPictureAddress,a.UserName,a.Sex,a.Age,a.ProvinceArea,a.StudyLevel,a.Earn
from UserTable a
join (select VisiterID,LastVisitTime=max(VisitTime) from VisitUsers where HostID=@UserID group by VisiterID) b
on a.UserID =b.VisiterID
order by b.LastVisitTime
因为VisiterUser表中才有访问时间啊,所以按照VisiterUser中的访问时间排序,,而且我还要遍历select VisiterID from VisitUsers where HostID=@UserID 查询出来的结果,请大虾再指点一下
where VisiterID=b.VisiterID
and COL>b.COL
的意思具体是什么呢?COL字段表示什么呢?还有 VisiterID=b.VisiterID是什么意思呢?
然后您的存储过程中where a.UserID =b.VisiterID
and b.HostID=@UserID
能遍历VisitUsers表中的符合要求的VisiterID的值么??请大虾耐心讲解,
小弟深表谢意!