mysql - 连接两个表并更新 MySql 中的第三个表

Question

我正在尝试连接两个表tblN1和tblN2来获取用户的总访问次数，并更新第三个表tblNCC，而无需成功，因为我有错误:

[Err] 1054 - Unknown column 't.UserN1' in 'on clause'

我不明白这个错误...列 t.UserN1 位于 Select sql 查询中。

也许问题出在 Union All 语法上？

我的代码如下，请帮助我。

提前非常感谢您。

UPDATE tblNCC AS CC
JOIN (
    SELECT
        sum(total) AS Total
    FROM
        (
            SELECT
                COUNT(*) AS Total,
                UserN1
            FROM
                `tblN1`
            WHERE
                UserN1 IN ('7047505')
            AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
            AND CURDATE()
            UNION ALL
                SELECT
                    COUNT(*) AS Total,
                    UserN2
                FROM
                    `tblN2`
                WHERE
                    UserN2 ('7047505')
                AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
                AND CURDATE()
        ) AS t
) AS q ON t.UserN1 = CC.UserNcc
SET CC.Total = t.Total;
[Err] 1054 - Unknown column 't.UserN1' in 'on clause'

Answer 1

t 不在范围内。它在q里面。

你的意思可能是这样的:

UPDATE tblNCC AS CC
JOIN (
    SELECT
        username,
        sum(total) AS Total
    FROM
        (
            SELECT
                COUNT(*) AS Total,
                UserN1 as username
            FROM
                `tblN1`
            WHERE
                UserN1 IN ('7047505')
            AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
            AND CURDATE()
            UNION ALL
                SELECT
                    COUNT(*) AS Total,
                    UserN2 as username
                FROM
                    `tblN2`
                WHERE
                    UserN2 ('7047505')
                AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
                AND CURDATE()
        ) AS t
) AS q ON q.username = CC.UserNcc
SET CC.Total = q.Total;