Question

请将我的SQL SERVER中的SQL语句转成我在ACCESS中使用的语句 我在ACCESS中使用老是提示：不正常的GUID ……

我的SQL语句是：
SELECT DISTINCT d.sysid, d.sysdesc, d.linkasp, d.sysdesc AS Expr1, c.prgid, c.prgdesc, c.linkasp AS sublink, d.seq, c.seq AS Expr2
FROM OA_SYY_M_POST AS a INNER JOIN
OA_SYY_M_MNU AS b ON a.grpid = SUBSTRING(b.usrid, 1, { LENGTH(a.grpid) }) INNER JOIN
OA_SYY_M_PRG AS c ON b.prgid = c.prgid INNER JOIN
OA_SYY_M_SYS AS d ON c.subid = d.sysid
WHERE (a.empno = '111') AND (c.prgflog = 0) OR
(c.prgflog = 0) AND (b.usrid = '0899')
ORDER BY d.seq, d.sysid, Expr2

这段SELECT语句在联接SQL server 数据库的时候一切正常，可是在联接ACCESS语句中就出错：
提示信息：
不正常得GUID。 在查询表达式'a.grpid = SUBSTRING(b.usrid, 1, { LENGTH(a.grpid) }) INNER JOIN
OA_SYY_M_PRG AS c ON b.prgid = c.prgid INNER JOIN
OA_SYY_M_SYS AS d ON c.subid = d.sysid 中

请教一下如果我在ACCESS中使用要怎么调整语句？非常感谢！

Answer 1

原因是ACCESS 数据库Jet引擎无法识别“SUBSTRING”函数和LENGTH函数所致
你可以用"Mid"函数取代“SUBSTRING”函数，"LEN"函数取代“LENGTH”函数即可，
具体如下：
SUBSTRING(b.usrid, 1, { LENGTH(a.grpid) })
改为
MID(b.usrid, 1, LEN(a.grpid))
整条SQL，这样写
SELECT DISTINCT d.sysid, d.sysdesc, d.linkasp, d.sysdesc AS Expr1, c.prgid, c.prgdesc, c.linkasp AS sublink, d.seq, c.seq AS Expr2
FROM OA_SYY_M_POST AS a INNER JOIN
OA_SYY_M_MNU AS b ON a.grpid =
Mid(b.usrid, 1, LEN(a.grpid))
INNER JOIN
OA_SYY_M_PRG AS c ON b.prgid = c.prgid INNER JOIN
OA_SYY_M_SYS AS d ON c.subid = d.sysid
WHERE (a.empno = '111') AND (c.prgflog = 0) OR
(c.prgflog = 0) AND (b.usrid = '0899')
ORDER BY d.seq, d.sysid, Expr2

修改后，Jet引擎就可以识别了，前提是ACCESS 数据表的结构与MSSQLSERVER的数据表结构完全相同。

追问

请问这样修改过后提示语法错误，是怎么回事？

追答

对了ACCESS一次只能够连接两张表（一个INNER join），你一次有3个INNER JOIN 当然不行了，语句要改为分次连接，具体怎样做挺复杂的。请告诉我，你的查询要达到什么目的，我重新帮你设计SQL语句，因为原语句，我实在没心情去作分析

要不你试一下，下面修改方案能否通过？不行再告诉我
SELECT DISTINCT d.sysid, d.sysdesc, d.linkasp, d.sysdesc AS Expr1, c.prgid, c.prgdesc, c.linkasp AS sublink, d.seq, c.seq AS Expr2
FROM ((OA_SYY_M_POST AS a INNER JOIN
OA_SYY_M_MNU AS b ON a.grpid =
Mid(b.usrid, 1, LEN(a.grpid)))
INNER JOIN
OA_SYY_M_PRG AS c ON b.prgid = c.prgid) INNER JOIN
OA_SYY_M_SYS AS d ON c.subid = d.sysid
WHERE (a.empno = '111') AND (c.prgflog = 0) OR
(c.prgflog = 0) AND (b.usrid = '0899')
ORDER BY d.seq, d.sysid,c.seq

Answer 2

数据类型：自动编号

常规-索引：有（无重复）

追问

能详细点儿嘛？

追答

图

追问

可是我的表里没有这个栏位啊？