笛卡尔积

笛卡尔积

关注
发信
关注(28)粉丝(399)

sql - SQLite找不到避免笛卡尔积的方法

我正在写一个查询，其中我在计算两个不同表的总和，这些表具有外键约束（1：n）。



因此，有一个表Kunde持有客户。每个Customer由一个Adm维护。每个Kunde具有N个不同的事务（PbsRow），而每个事务包含N个不同的产品（WarengruppeVK）。每笔交易都有一个月和一年（Monat和Jahr）

我需要的是包含以下信息的结果：
1）一个Adm的名称，2）属于该特定Adm客户之一的所有sollfracht和handling值在2013年的总和，3）属于该Adm客户的所有netto和db_basis值的总和该客户在2013年的PbsRow之一，4和5）与2）和3）相同，仅在2012年

我已经尝试了不同的方法，但是当我对下一个表进行联接时，我总是最终得到具有sollfracht和handling值的笛卡尔积。

请看一下我的查询：

SELECT vj.*,
       j.*,
       adm.ZNAME
FROM ZADM adm,
  (SELECT k.ZADMITARBEITER AS admidvj,
          SUM(vk.ZNETTO) AS summeVJ,
          SUM(vk.ZDB_BASIS) AS summeDBVJ,
          SUM(p.ZSOLLFRACHT) AS sollfrachtVJ,
          SUM(p.ZHANDLING) AS handlingVJ
   FROM ZWARENGRUPPEVK vk
   LEFT JOIN ZPBSROW p ON p.Z_PK=vk.ZPBSROW
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2012
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) vj,
  (SELECT k.ZADMITARBEITER AS admidj,
          SUM(vk.ZNETTO) AS summeJ,
          SUM(vk.ZDB_BASIS) AS summeDBJ,
          SUM(p.ZSOLLFRACHT) AS sollfrachtJ,
          SUM(p.ZHANDLING) AS handlingJ
   FROM ZWARENGRUPPEVK vk
   LEFT JOIN ZPBSROW p ON p.Z_PK=vk.ZPBSROW
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2013
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) j
WHERE vj.admidvj=j.admidj
  AND vj.admidvj=adm.Z_PK


如何避免使用这种笛卡尔积？当我从结果中删除WarengruppeVK表时，sollfracht和handling值正确。

提前致谢。

编辑：
这是一些样品。

这是我从上面的查询中得到的结果：


这是我删除第一个联接时的结果：


您会发现sollfrachtVJ和handlingVJ现在不同了。它们取自笛卡尔积所在的PbsRow。因此，这两个值实际上是正确的值，但是我还需要我已注释掉的两个值的总和。

这是我删除了一个联接之后的SQL语句：

SELECT vj.*,
       j.*,
       adm.ZNAME
FROM ZADM adm,
  (SELECT k.ZADMITARBEITER AS admidvj,
          --          SUM(vk.ZNETTO) AS summeVJ,
--          SUM(vk.ZDB_BASIS) AS summeDBVJ,

          SUM(p.ZSOLLFRACHT) AS sollfrachtVJ,
          SUM(p.ZHANDLING) AS handlingVJ
   FROM -- ZWARENGRUPPEVK vk
 ZPBSROW p -- LEFT JOIN ZPBSROW p ON p.Z_PK=vk.ZPBSROW

   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2012
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) vj,
  (SELECT k.ZADMITARBEITER AS admidj,
          SUM(vk.ZNETTO) AS summeJ,
          SUM(vk.ZDB_BASIS) AS summeDBJ,
          SUM(p.ZSOLLFRACHT) AS sollfrachtJ,
          SUM(p.ZHANDLING) AS handlingJ
   FROM ZWARENGRUPPEVK vk
   LEFT JOIN ZPBSROW p ON p.Z_PK=vk.ZPBSROW
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2013
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) j
WHERE vj.admidvj=j.admidj
  AND vj.admidvj=adm.Z_PK


编辑2

好的，这是包含正确结果的SQL语句，但是缺少4列。

SELECT vj.*,
       j.*,
       adm.ZNAME
FROM ZADM adm,
  (SELECT k.ZADMITARBEITER AS admidvj,
          SUM(p.ZSOLLFRACHT) AS sollfrachtVJ,
          SUM(p.ZHANDLING) AS handlingVJ
   FROM ZPBSROW p
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2012
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) vj,
  (SELECT k.ZADMITARBEITER AS admidj,
          SUM(p.ZSOLLFRACHT) AS sollfrachtJ,
          SUM(p.ZHANDLING) AS handlingJ
   FROM ZPBSROW p
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2013
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) j
WHERE vj.admidvj=j.admidj
  AND vj.admidvj=adm.Z_PK


如您所见，不包含summeJ，summeVJ，summeDBJ和summeDBVJ，这就是问题所在。此结果中的所有值都是正确的，但我还需要在结果中包含这4个值。我上面结果的第一个屏幕截图包含正确的summeJ，summeVJ，summeDBJ和summeDBVJ值，但是错误的handlingJ，handlingVJ，sollfrachtJ和sollfrachtVJ值。

编辑3：

我终于找到了一种方法。这是有效的查询。这只是几个子查询：

    SELECT ((summeJ-summeVJ)/summeVJ*100) AS abwNetto,
       (summeJ-summeVJ) AS abwNettoAbs,
       ((summeDBJ-summeDBVJ)/summeDBVJ*100) AS abwDB,
       (summeDBJ-summeDBVJ) AS abwDBAbs,
       t0.*,
       t1.*,
       adm.ZNAME
FROM ZADM adm,
  (SELECT vj.*,
          j.*
   FROM
     (SELECT k.ZADMITARBEITER AS admidvj,
             SUM(p.ZSOLLFRACHT) AS sollfrachtVJ,
             SUM(p.ZHANDLING) AS handlingVJ
      FROM ZPBSROW p
      LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
      WHERE ZJAHR=2012
        AND ZMONAT>=1
        AND ZMONAT<=6
      GROUP BY k.ZADMITARBEITER) vj
   LEFT JOIN
     (SELECT k.ZADMITARBEITER AS admidj,
             SUM(p.ZSOLLFRACHT) AS sollfrachtJ,
             SUM(p.ZHANDLING) AS handlingJ
      FROM ZPBSROW p
      LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
      WHERE ZJAHR=2013
        AND ZMONAT>=1
        AND ZMONAT<=6
      GROUP BY k.ZADMITARBEITER) j ON vj.admidvj = j.admidj) t0,
  (SELECT vj.*,
          j.*
   FROM
     (SELECT k.ZADMITARBEITER AS admidvj,
             SUM(vk.ZNETTO) AS summeVJ,
             SUM(vk.ZDB_BASIS) AS summeDBVJ
      FROM ZPBSROW p
      LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=p.Z_PK
      LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
      WHERE ZJAHR=2012
        AND ZMONAT>=1
        AND ZMONAT<=6
      GROUP BY k.ZADMITARBEITER) vj
   LEFT JOIN
     (SELECT k.ZADMITARBEITER AS admidj,
             SUM(vk.ZNETTO) AS summeJ,
             SUM(vk.ZDB_BASIS) AS summeDBJ
      FROM ZPBSROW p
      LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=p.Z_PK
      LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
      WHERE ZJAHR=2013
        AND ZMONAT>=1
        AND ZMONAT<=6
      GROUP BY k.ZADMITARBEITER) j ON vj.admidvj = j.admidj) t1
WHERE t0.admidvj=t1.admidvj
  AND t0.admidvj=adm.Z_PK

最佳答案

您的联接的问题是所有事物都联接在一起。
您应该改用独立的标量子查询：

SELECT name,
       (SELECT SUM(WarengruppeVK.netto)
        FROM Kunde
        JOIN PbsRow ON Kunde.PK = PbsRow.kunde
        JOIN WarengruppeVK ON PbsRow.PK = WarengruppeVK.pbsrow
        WHERE Kunde.admitarbeiter = Adm.PK
          AND PbsRow.jahr = 2012
       ) AS vj_netto,
       (SELECT SUM(PbsRow.sollfracht)
        FROM Kunde
        JOIN PbsRow ON Kunde.PK = PbsRow.kunde
        WHERE Kunde.admitarbeiter = Adm.PK
          AND PbsRow.jahr = 2012
       ) AS vj_sollfracht
       (SELECT SUM(WarengruppeVK.netto)
        FROM Kunde
        JOIN PbsRow ON Kunde.PK = PbsRow.kunde
        JOIN WarengruppeVK ON PbsRow.PK = WarengruppeVK.pbsrow
        WHERE Kunde.admitarbeiter = Adm.PK
          AND PbsRow.jahr = 2013
       ) AS j_netto,
       (SELECT SUM(PbsRow.sollfracht)
        FROM Kunde
        JOIN PbsRow ON Kunde.PK = PbsRow.kunde
        WHERE Kunde.admitarbeiter = Adm.PK
          AND PbsRow.jahr = 2013
       ) AS j_sollfracht
FROM Adm

07-27 17:45
Powered by LMLPHP ©2024 笛卡尔积 0.029286