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

避免通过执行多个连接来运行多个查询,这是不可能的

ms-access sql

Jimmy · 技术社区 · 7 年前

我真的被这个SQL困住了。我有两个MS Access数据库表,设置如下:

数据

ID
UID
MYNAME

蒙塔塔

ID
DATAUID
MONTHVAL
VAL

我不想改变这个表的结构。我在monthdata表中有几个月的数据,它与数据表中的引用相关联。

我试图在一个月内返回三个月的数据这样地:

UID - MONTHVAL - VAL - MONTHVAL - VAL - MONTHVAL - VAL

因此,如果我们想获取UID ABC123的最后三个月的数据,我尝试运行以下查询:

SELECT data.uid, monthdata.monthval, monthdata.val
FROM data
LEFT JOIN monthdata ON data.UID = monthdata.dataUID AND monthdata.monthval = "01/09/2018"
LEFT JOIN monthdata ON data.UID = monthdata.dataUID AND monthdata.monthval = "01/08/2018"
LEFT JOIN monthdata ON data.UID = monthdata.dataUID AND monthdata.monthval = "01/07/2018"
WHERE UID = 'ABC123'

但是它告诉我这是无效的语法。然而,尽管我在谷歌上搜索,我还是找不到我该怎么做。我能看到的唯一选择是运行三个独立的查询,并将日期放在WHERE中,但在现实世界中,我有更多的月份。

3 回复 | 直到 7 年前

-1

Gordon Linoff 7 年前

SELECT d.uid, md_20180901.val, md_20180801.val, 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
      ) 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 = "ABC123";

您还可以使用条件聚合来表示这一点:

SELECT d.uid,
       MAX(IIF(md.monthval = "01/09/2018", val, NULL)) as mv_20180901,
       MAX(IIF(md.monthval = "01/08/2018", val, NULL)) as mv_20180801,
       MAX(IIF(md.monthval = "01/07/2018", val, NULL)) as mv_20180701
FROM data as d LEFT JOIN
     monthdata as md
     ON d.UID = md.dataUID 
WHERE d.UID = "ABC123"
GROUP BY d.UID;

Salman Arshad 7 年前

当你加入一个表更多次时(就像你用 monthval )每次都必须使用不同的别名。当然,在这种情况下,更改DB模式是您应该考虑的问题,不管语法错误来自缺少的别名,请尝试以下操作:

SELECT D.uid, M.monthval, M1.monthval, M2.monthval
FROM ((data AS D
LEFT JOIN monthdata AS M ON D.UID = M.dataUID AND M.monthval = #01/09/2018#)
LEFT JOIN monthdata AS M1 ON D.UID = M1.dataUID AND M1.monthval = #01/08/2018#) 
LEFT JOIN monthdata AS M2 ON D.UID = M2.dataUID AND M2.monthval = #01/07/2018#
WHERE UID = 'ABC123';

M , M1 M2 )一个是数据( D ),在和 平方米 ,但你可以放你需要的。

Fahmi 7 年前

请在下面尝试使用行号()和大小写

    select uid, 
    max(case when  rn=1 then  monthval end) as month1, 
    max(case when  rn=1 then  val end) as val1,
    max(case when  rn=2 then  monthval end) as month2,
    max(case when  rn=2 then  val end) as val2,
    max(case when  rn=3 then  monthval end) as month3, 
    max(case when  rn=3 then  val end) as val3
    from
    (
      SELECT data.uid, monthdata.monthval, monthdata.val,row_number() over(order by uid) as rn FROM data
      LEFT JOIN monthdata ON data.UID = monthdata.dataUID and monthdata.monthval between '01/07/2018' and '01/09/2018'
WHERE UID = 'ABC123')a
      group by uid