mysql - SQL JOIN进行计数和求和

我知道我缺少了一些东西，我尝试了几种不同的方法，但我却忽略了（或太努力了）。有人可以告诉我此SQL错误吗？

SELECT id,
    COUNT(id) AS dupBlocks
FROM tbl_duplicates8 INNER JOIN (
        tbl_accounts8,
        tbl_delaccounts,
        tbl_bad_bots,
        tbl_log,
        tbl_ipban,
        tbl_ipban8
    ) ON (
        tbl_accounts8.SUM(num_attacks) AND
        tbl_delaccounts.SUM(noattacks) AND
        tbl_bad_bots.COUNT(id) AND
        tbl_log.COUNT(id) AND
        tbl_ipban.COUNT(txt_ip) AND
        tbl_ipban8.COUNT(ip)
    );

我确实注意到了这个MySQL Join two tables count and sum from second table，但返回时为空。

任何帮助，将不胜感激。

为了进一步解答这个问题，我现在正在做的是：

$statsresults['newIPBan'] = $db->query("SELECT COUNT(ip) AS newIPBan FROM tbl_ipban8;");
$statsresults['oldIPBan'] = $db->query("SELECT COUNT(txt_ip) AS oldIPBan FROM tbl_ipban;");
$statsresults['log_blocks'] = $db->query("SELECT COUNT(id) AS logBlocks FROM tbl_log;");
$statsresults['badbots'] = $db->query("SELECT COUNT(id) AS badBots FROM tbl_bad_bots;");
$statsresults['del_num_attacks'] = $db->query("SELECT SUM(noattacks) AS deltotalattacks FROM tbl_delaccounts;");
$statsresults['num_attacks'] = $db->query("SELECT SUM(num_attacks) AS totalattacks FROM tbl_accounts8;");
$statsresults['dup_blocks'] = $db->query("SELECT COUNT(id) AS dupBlocks FROM tbl_duplicates8;");

这将返回此：

|    ['newIPBan0newIPBan'] = String(6) "289033"
|    ['oldIPBan0oldIPBan'] = String(6) "125723"
|    ['log_blocks0logBlocks'] = String(4) "6481"
|    ['badbots0badBots'] = String(5) "15310"
|    ['del_num_attacks0deltotalattacks'] = String(9) "119494860"
|    ['num_attacks0totalattacks'] = String(8) "25286478"
|    ['dup_blocks0dupBlocks'] = String(6) "179916"

因此，现在它正在调用数据库7次以获取每个总和或计数。我希望将其更改为1个数据库调用，并返回它们的总和。

最佳答案

这是组合它们的一种方法：

select (newIPBan + oldIPBan + logBlocks + badBots + deltotalattacks + totalattacks + dupBlocks
       ) as NumIPs
from (SELECT COUNT(ip) AS oldIPBan FROM tbl_ipban8) ipb8 cross join
     (SELECT COUNT(txt_ip) AS newIPBan FROM tbl_ipban) ipb cross join
     (SELECT COUNT(id) AS logBlocks FROM tbl_log) l cross join
     (SELECT COUNT(id) AS badBots FROM tbl_bad_bots) bb cross join;
     (SELECT coalesce(SUM(noattacks), 0) AS deltotalattacks FROM tbl_delaccounts) da cross join
     (SELECT coalesce(SUM(num_attacks), 0) AS totalattacks FROM tbl_accounts8) ta cross join
     (SELECT COUNT(id) AS dupBlocks FROM tbl_duplicates8) d;

关于mysql - SQL JOIN进行计数和求和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21459920/