带有SUM()的3个表的内连接

Question

我在试图连接三张桌子时遇到了问题：

表用户:用户标识，上限(ADSL bandwidth)

• Table会计: userid，sessiondate，bandwidth

• Table adhoc: userid，date，

)

我希望有一个查询，返回一组所有用户、其上限、本月使用的带宽和本月的临时购买：

< TABLE 1 ><TABLE2><TABLE3>
User   | Cap | Adhoc | Used
marius | 3   | 1     | 3.34
bob    | 1   | 2     | 1.15
(simplified)

下面是我正在处理的查询：

SELECT
        `msi_adsl`.`id`,
        `msi_adsl`.`username`,
        `msi_adsl`.`realm`,
        `msi_adsl`.`cap_size` AS cap,
        SUM(`adsl_adhoc`.`value`) AS adhoc,
        SUM(`radacct`.`AcctInputOctets` + `radacct`.`AcctOutputOctets`) AS used
FROM
        `msi_adsl`
INNER JOIN
        (`radacct`, `adsl_adhoc`)
ON
        (CONCAT(`msi_adsl`.`username`,'@',`msi_adsl`.`realm`) 
           = `radacct`.`UserName` AND `msi_adsl`.`id`=`adsl_adhoc`.`id`)

WHERE
        `canceled` = '0000-00-00'
AND
        `radacct`.`AcctStartTime`
BETWEEN
        '2010-11-01'
AND
        '2010-11-31'
AND
        `adsl_adhoc`.`time`
BETWEEN
        '2010-11-01 00:00:00'
AND
        '2010-11-31 00:00:00'
GROUP BY
        `radacct`.`UserName`, `adsl_adhoc`.`id` LIMIT 10

该查询工作正常，但它为adhoc和used返回错误的值；我的猜测是联接中的逻辑错误，但我看不到它。任何帮助都是非常感谢的。

Answer 1

您的查询布局太分散，不适合我的口味。特别是，中间/和条件应该是每行一行，而不是每行5行。我也删除了后排，虽然你可能需要他们的“时间”栏。

由于您的表布局与示例查询不匹配，这会使生活变得非常困难。然而，表布局都包含一个UserID (这是合理的)，所以我编写了这个查询，以便使用UserID进行相关的连接。正如我在评论中指出的那样，如果您的设计需要使用串接操作来连接两个表，那么您就有了性能灾难的解决方案。按照表布局的建议，更新实际模式，使表可以由UserID连接。显然，您可以在联接中使用函数结果，但是(除非DBMS支持“functional”并创建适当的索引)，DBMS将无法在计算函数的表上使用索引来加快查询速度。对于一次性查询，这可能无关紧要；对于生产查询，这通常非常重要。

这有可能完成你想要的工作。由于您在两个表上进行聚合，所以需要FROM子句中的两个子查询。

SELECT u.UserID,
       u.username,
       u.realm,
       u.cap_size AS cap,
       h.AdHoc,
       a.OctetsUsed
  FROM msi_adsl AS u
  JOIN (SELECT UserID, SUM(AcctInputOctets + AcctOutputOctets) AS OctetsUsed
          FROM radact
         WHERE AcctStartTime BETWEEN '2010-11-01' AND '2010-11-31'
         GROUP BY UserID
       )    AS a ON a.UserID = u.UserID
  JOIN (SELECT UserID, SUM(Value) AS AdHoc
          FROM adsl_adhoc
         WHERE time BETWEEN '2010-11-01 00:00:00' AND '2010-11-31 00:00:00'
         GROUP BY UserId
       )    AS h ON h.UserID = u.UserID
 WHERE u.canceled = '0000-00-00'
 LIMIT 10

每个子查询计算指定时间段内每个用户的聚合值，生成UserID和聚合值作为输出列；然后，主查询只从主用户表中提取正确的用户数据并与聚合子查询连接。

Answer 2

我认为问题就在这里

FROM  `msi_adsl`
INNER JOIN
        (`radacct`, `adsl_adhoc`)
ON
        (CONCAT(`msi_adsl`.`username`,'@',`msi_adsl`.`realm`)
           = `radacct`.`UserName` AND `msi_adsl`.`id`=`adsl_adhoc`.`id`)

您正在将连接与笛卡尔产品混合使用，这不是一个好主意，因为调试起来要困难得多。试试这个：

FROM  `msi_adsl`
INNER JOIN
        `radacct`
ON
      CONCAT(`msi_adsl`.`username`,'@',`msi_adsl`.`realm`) = `radacct`.`UserName`
JOIN  `adsl_adhoc` ON  `msi_adsl`.`id`=`adsl_adhoc`.`id`