代码之家 › 专栏 › 技术社区 › Jimmy

不支持Access SQL联接表达式

ms-access sql

Jimmy · 技术社区 · 6 年前

SELECT
   d.uid,
   md_20180901.monthval,
   md_20180801.monthval,
   md_20180701.val 
FROM
   (
(data as d 
      LEFT JOIN
         (
            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/09/2018# 
         )
         as md_20180901 
         ON d.UID = md_20180901.dataUID 
         AND md_20180901.monthval = #01/09/2018# ) 
      LEFT JOIN
         (
            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/08/2018# 
         )
         as md_20180801 
         ON d.UID = md_20180801.dataUID 
         AND md_20180801.monthval = #01/08/2018#
   )
   LEFT JOIN
      (
         SELECT
            md.* 
         FROM
            monthdata md 
         WHERE
            md.monthval = #01/07/2018# 
      )
      as md_20180701 
      ON d.UID = md_20180701.dataUID 
      AND md_20180701.monthval = #01/07/2018#
WHERE
   d.UID = "5B51141D";

作为上下文,这是我的表

桌子

ID
UID
MYNAME

蒙塔塔 桌子

ID
DATAUID
MONTHVAL
VAL

2 回复 | 直到 6 年前

Erik A 6 年前

Access仅支持连接表达式中由括号括起的文字:

SELECT
   d.uid,
   md_20180901.monthval,
   md_20180801.monthval,
   md_20180701.val 
FROM
   (
(data as d 
      LEFT JOIN
         (
            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/09/2018# 
         )
         as md_20180901 
         ON (d.UID = md_20180901.dataUID 
         AND md_20180901.monthval = #01/09/2018#) ) 
      LEFT JOIN
         (
            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/08/2018# 
         )
         as md_20180801 
         ON (d.UID = md_20180801.dataUID 
         AND md_20180801.monthval = #01/08/2018#)
   )
   LEFT JOIN
      (
         SELECT
            md.* 
         FROM
            monthdata md 
         WHERE
            md.monthval = #01/07/2018# 
      )
      as md_20180701 
      ON (d.UID = md_20180701.dataUID 
      AND md_20180701.monthval = #01/07/2018#)
WHERE
   d.UID = "5B51141D";

但是,你在做一些奇怪的事情。您还可以在子查询内部进行比较。删除以下内容:

SELECT
   d.uid,
   md_20180901.monthval,
   md_20180801.monthval,
   md_20180701.val 
FROM
(
(data as d 
      LEFT JOIN
         monthdata as md_20180901 
         ON (d.UID = md_20180901.dataUID 
         AND md_20180901.monthval = #01/09/2018#) ) 
      LEFT JOIN
         monthdata as md_20180801 
         ON (d.UID = md_20180801.dataUID 
         AND md_20180801.monthval = #01/08/2018#)
   )
   LEFT JOIN
      monthdata as md_20180701 
      ON (d.UID = md_20180701.dataUID 
      AND md_20180701.monthval = #01/07/2018#)
WHERE
   d.UID = "5B51141D";

SunKnight0 6 年前

从移除所有不必要的部分开始,让它变得更简单。例子:

            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/09/2018# 
         )
         as md_20180901 
         ON d.UID = md_20180901.dataUID 
         AND md_20180901.monthval = #01/09/2018#

(SELECT val FROM monthdata WHERE monthval=#01/09/2018#) md_20180901
ON d.UID=md_20180901.dataUID

除非存在字段名冲突,否则不需要重复表名。您不需要指定永远不会使用的别名(事实上,我通常只在使用同一个表两次时才指定别名)。 monthval 论网络过滤 ON 因为你已经在做了 WHERE . 你实际收集的唯一字段是 val ( 蒙特瓦尔 实际上是一个固定值,因为您是按特定日期筛选的),所以不要使用 * .

清理查询并尝试添加一个看看会发生什么。