mysql - 使用 SUM() 在 3 个表上进行 INNER JOIN

Question

我在尝试连接总共三个表时遇到问题:

• 用户表:userid，cap(ADSL带宽)
• 表记帐:userid、sessiondate、使用的带宽
• 临时表:用户 ID、日期、购买金额

我想要 1 个查询返回一组所有用户、他们的上限、他们本月使用的带宽以及他们本月的临时购买:

< 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

您的查询布局过于分散，不符合我的口味。特别是，BETWEEN/AND 条件每个应该在 1 行，而不是每个 5 行。我还删除了反引号，但“时间”列可能需要它们。

由于您的表格布局与您的示例查询不匹配，这让工作变得非常困难。但是，表布局都包含一个 UserID(这是合理的)，因此我编写了查询以使用 UserID 进行相关连接。正如我在评论中指出的那样，如果您的设计需要使用 CONCAT 操作来连接两个表，那么您就有了性能灾难的秘诀。更新您的实际架构，以便表可以通过 UserID 连接，正如您的表布局所建议的那样。显然，您可以在连接中使用函数结果，但是(除非您的 DBMS 支持“功能索引”并且您创建了适当的索引)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和聚合值作为输出列；主查询然后简单地从主用户表中提取正确的用户数据并与聚合子查询连接。