mysql - 连接两张表获取用户在MySql中的总访问数

Question

在我的 MySql 中，我有两个表:tblN1 和 tblN2。

tblN1 表是一个旧表，其中存储了网站限制区域中的帐户访问权限。

表tblN2是一个新表，其中存储了网站限制区域中的帐户访问权限。

如果在 tblN1 上尝试此查询:

mysql> SELECT
    COUNT(*) AS TotalN1
FROM
    `tblN1`
WHERE
    UserN1 IN ('7047505')
AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND CURDATE();
+---------+
| TotalN1 |
+---------+
|       4 |
+---------+
1 row in set

此查询的返回值是4。

如果在 tblN2 上尝试这个查询:

mysql> SELECT
    COUNT(*) as TotalN2
FROM
    `tblN2`
WHERE
    UserN2 IN ('7047505')
AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND CURDATE();
+---------+
| TotalN2 |
+---------+
|       0 |
+---------+
1 row in set

此查询的返回值是0。

我正在尝试连接两个表以获得用户的总访问数。

mysql> SELECT
    (
        COUNT(DISTINCT A.ID) + COUNT(DISTINCT CA.ID)
    ) AS SuperTotal,
    COUNT(DISTINCT A.ID) AS TotalN1,
    COUNT(DISTINCT CA.ID) AS TotalN2,
    UserN1 AS UserN1,
    UserN2  AS UserN2
FROM
    `tblN1` A
JOIN `tblN2` CA ON A.UserN1 = CA.UserN2
WHERE
    CA.UserN2 IN ('7047505')
AND (
    dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
    AND CURDATE()
    AND `dateN2` BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
    AND CURDATE()
);
+------------+---------+---------+--------+--------+
| SuperTotal | TotalN1 | TotalN2 | UserN1 | UserN2 |
+------------+---------+---------+--------+--------+
|          0 |       0 |       0 | NULL   | NULL   |
+------------+---------+---------+--------+--------+
1 row in set

所有字段的返回值都是0，为什么？

请帮助我，在此先感谢您。

编辑 #1

mysql> SELECT sum(total) as Total FROM
    (SELECT COUNT(*) AS Total
    FROM `tblN1`
    WHERE UserN1 IN ('7047505')
          AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1) AND CURDATE()
    UNION ALL
    SELECT COUNT(*)
    FROM `tblN2`
    WHERE UserN2 IN ('7047505')
          AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1) AND CURDATE()
    ) AS t;


+-------+
| Total |
+-------+
| 0     |
+-------+
1 row in set

Answer 1

连接导致返回 0。从您的示例中可以清楚地看出，给定用户在第二个表中没有任何满足 where 条件的记录。当您根据用户 ID 将第一个表连接到第二个表时，第二个表不会返回任何结果，因此连接会从第一个表中删除相应的记录。

更新:

不使用 join，只需在子查询中将 2 个查询与 union 结合起来，然后在外部查询中将它们相加:

SELECT sum(total) as Total FROM
    (SELECT COUNT(*) AS Total
    FROM `tblN1`
    WHERE UserN1 IN ('7047505')
          AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1) AND CURDATE()
    UNION ALL
    SELECT COUNT(*)
    FROM `tblN2`
    WHERE UserN2 IN ('7047505')
          AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1) AND CURDATE()
    ) AS t