a表结构x,y,z,id(y是主键,id是递增int)
b表结构t,w,m,x,e,ip;(t是主键,ip是递增int)
数据库sql srver 2000要求:返回字段y,z,t,w,e的字段值.
以b表的ip为顺序
每次得到3行结果,用于分页显示
使用以下语句得到4,5,6条记录,出错:
SELECT TOP 3* from a,b WHERE a.x=b.x AND b.ip not in(SELECT TOP 3* b.ip from a,b WHERE a.x=b.x order by b.ip) order by b.ip
错误提示:不能重读a表的y,z字段
请帮忙!!!
b表结构t,w,m,x,e,ip;(t是主键,ip是递增int)
数据库sql srver 2000要求:返回字段y,z,t,w,e的字段值.
以b表的ip为顺序
每次得到3行结果,用于分页显示
使用以下语句得到4,5,6条记录,出错:
SELECT TOP 3* from a,b WHERE a.x=b.x AND b.ip not in(SELECT TOP 3* b.ip from a,b WHERE a.x=b.x order by b.ip) order by b.ip
错误提示:不能重读a表的y,z字段
请帮忙!!!
A.x, A.z,
B.t, B.w, B.e
FROM A, B
WHERE A.x = b.x
AND B.t NOT IN(
SELECT TOP 3
B.T
FROM A, B
WHERE A.x = b.x
ORDER BY B.ip)
ORDER BY B.ip
@Page int -- 要查询第x页
AS
SET @page = @page * 3 - 3
EXEC('
SELECT TOP 3
A.x, A.z,
B.t, B.w, B.e
FROM A, B
WHERE A.x = b.x
AND B.t NOT IN(
SELECT TOP ' + @Page +'
B.T
FROM A, B
WHERE A.x = b.x
ORDER BY B.ip)
ORDER BY B.ip')