三张表 jtstock,product,salepart 联合查询, 第一页的sql语句是:正常显示 SELECT TOP 1 [jtstock].id,[jtstock].pid,[jtstock].partid,[jtstock].isjt,[jtstock].pnum,[jtstock].addtime,[jtstock].issh,[product].id,[product].pname,[product].ptype,[salepart].id,[salepart].partname FROM jtstock INNER JOIN product ON jtstock.pid = product.id INNER JOIN salepart ON jtstock.partid=salepart.id ORDER BY jtstock.id ASC
从第二页开始sql语句是: sql = "SELECT [jtstock].id,[jtstock].pid,[jtstock].partid,[jtstock].isjt,[jtstock].pnum,[jtstock].addtime,[jtstock].issh,[product].id,[product].pname,[product].ptype,[salepart].id,[salepart].partname FROM (SELECT TOP 1 [jtstock].id,[jtstock].pid,[jtstock].partid,[jtstock].isjt,[jtstock].pnum,[jtstock].addtime,[jtstock].issh,[product].id,[product].pname,[product].ptype,[salepart].id,[salepart].partname FROM jtstock INNER JOIN product ON jtstock.pid = product.id INNER JOIN salepart ON jtstock.partid=salepart.id WHERE jtstock.id<(SELECT MIN(jtstock.id) FROM (SELECT TOP 1 jtstock.id FROM jtstock INNER JOIN product ON jtstock.pid = product.id INNER JOIN salepart ON jtstock.partid=salepart.id ORDER BY jtstock.id DESC) AS derivedtbl_1) ORDER BY jtstock.id DESC) AS derivedtbl_2 ORDER BY jtstock.id ASC" 总是报错:列前缀 jtstock 与查询中所用的表名或别名不匹配。
从第二页开始sql语句是: sql = "SELECT [jtstock].id,[jtstock].pid,[jtstock].partid,[jtstock].isjt,[jtstock].pnum,[jtstock].addtime,[jtstock].issh,[product].id,[product].pname,[product].ptype,[salepart].id,[salepart].partname FROM (SELECT TOP 1 [jtstock].id,[jtstock].pid,[jtstock].partid,[jtstock].isjt,[jtstock].pnum,[jtstock].addtime,[jtstock].issh,[product].id,[product].pname,[product].ptype,[salepart].id,[salepart].partname FROM jtstock INNER JOIN product ON jtstock.pid = product.id INNER JOIN salepart ON jtstock.partid=salepart.id WHERE jtstock.id<(SELECT MIN(jtstock.id) FROM (SELECT TOP 1 jtstock.id FROM jtstock INNER JOIN product ON jtstock.pid = product.id INNER JOIN salepart ON jtstock.partid=salepart.id ORDER BY jtstock.id DESC) AS derivedtbl_1) ORDER BY jtstock.id DESC) AS derivedtbl_2 ORDER BY jtstock.id ASC" 总是报错:列前缀 jtstock 与查询中所用的表名或别名不匹配。
<%
Dim myListPager
Set myListPager = new ListPager
with myListPager
.Connection = conn '//链接字段
.Table = "jtstock INNER JOIN product ON jtstock.pid = product.id INNER JOIN salepart ON jtstock.partid=salepart.id" '//表名
.PrimaryKey = "jtstock.id" '//主键
.Fields = "[jtstock].id,[jtstock].pid,[jtstock].partid,[jtstock].isjt,[jtstock].pnum,[jtstock].addtime,[jtstock].issh,[product].id,[product].pname,[product].ptype,[salepart].id,[salepart].partname" '//读取字段
.PageSize = 1 '//每页显示
%>每页一条数据,第一页正常,第二页就开始报错了
把外第二层的 [jtstock].改成derivedtbl_1.或者去掉
<%
Dim myListPager
Set myListPager = new ListPager
with myListPager
.Connection = conn '//链接字段
.Table = "jtstock INNER JOIN product ON jtstock.pid = product.id INNER JOIN salepart ON jtstock.partid=salepart.id" '//表名
.PrimaryKey = "jtstock.id" '//主键
.Fields = "[jtstock].id,[jtstock].pid,[jtstock].partid,[jtstock].isjt,[jtstock].pnum,[jtstock].addtime,[jtstock].issh,[product].id,[product].pname,[product].ptype,[salepart].id,[salepart].partname" '//读取字段
.PageSize = 1 '//每页显示
%>
这样的哦,那些长的sql语句是这个程序生成的
1.在SQL语句中一旦指定了表别名,则不能再使用"表名称.列名"格式,只能使用"表别名.列名"格式来引用列.2.在SQL主句中使用子查询中的列时,必须为子查询指定一个表别名,然后以"表别名.列名"的格式来应用子查询中的列.当SQL主句的FROM子句仅为一个子查询时,必须为该子查询指定一个表别名,否则SQL语句无法执行.例如:
SELECT ID FROM (select e.ID from employee as e where (e.年度 = 2000) AND (e.月份 = 3))
提示: ')' 附近有语法错误。正确的应该为:
SELECT ID FROM (select e.ID from employee as e where (e.年度 = 2000) AND (e.月份 = 3)) AS X3.子查询中使用了"表别名.列名"来引用列时,若此表别名在子查询和SQL主句中都不存在,则导致SQL语句无法执行.例如:
SELECT ID FROM (select e.ID from employee where (e.年度 = 2000) AND (e.月份 = 3)) AS X
提示: 列前缀 'e' 与查询中所用的表名或别名不匹配。
如果SQL主句中存在此表别名,则错误不会发生,但会导致无法预料的结果(通常导致该子查询返回结果为空).例如:
SELECT e.* FROM (select * from sales as e where (e.年度 = 2006) and (e.月份 = 3)) AS e
WHERE (e.员工号 NOT IN (select e.员工号 from sales where (e.年度 = 2000) AND (e.月份 = 3)))
此SQL语句执行后,导致主句的WHERE子句的子查询返回空值,使WHERE子句结果为真,筛选实效.
正确的为:
SELECT e.* FROM (select * from sales as e where (e.年度 = 2006) and (e.月份 = 3)) AS e
WHERE (e.员工号 NOT IN (select e.员工号 from sales as e where (e.年度 = 2000) AND (e.月份 = 3)))
说明你数据库里没有jtstock 表嘛,而且jtstock 也不是表别名
你的jtstock.id 就执行不过去了啊