MySQL:无法访问临时表

Question

我已经编写了这个 MySQL 存储过程(问题末尾的完整过程)，它在以下语句中给了我错误:

UPDATE TrialBalTbl
    SET totalCred = (SELECT SUM(DISTINCT L1CredSUM) FROM TrialBalTbl),
         totalDeb = (SELECT SUM(DISTINCT L1DebSUM) FROM TrialBalTbl);

错误是:

Can't reopen table

(TrialBalTbl 是一个临时表)我搜索了它并发现 that

You cannot refer to a TEMPORARY table more than once in the same query

所以我想到使用临时表TrialBalTbl的别名tbal来达到效果，并想出了这个:

UPDATE TrialBalTbl as tbal
SET totalCred = (SELECT SUM(DISTINCT L1CredSUM) FROM tbal),
        totalDeb = (SELECT SUM(DISTINCT L1DebSUM) FROM tbal);

不幸的是，这也不起作用。它说

Table 'digitalmanager.tbal' doesn't exist

谁能告诉我这是怎么回事吗？我怎样才能让它发挥作用？这是我的完整存储过程，直到出现此 UPDATE 语句为止:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Trial_Balance`(IN startDate Varchar(200), IN endDate Varchar(200))
BEGIN

DROP TABLE IF EXISTS digitalmanager.TrialBalTbl;

CREATE TEMPORARY TABLE TrialBalTbl (
    CTR INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    PARTY_ID VARCHAR(100),
    ACCOUNT_ID VARCHAR(100),
    PARTY_NAME VARCHAR(500),
    DEBIT DECIMAL(19,2),
    CREDIT DECIMAL(19,2),
    L1  VARCHAR(200),
    L1NAME VARCHAR(1000),
    L2 VARCHAR(200),
    L2NAME VARCHAR(1000),
    L3 VARCHAR(200),
    L3NAME VARCHAR(1000),
    L1DebSUM decimal(19,2),
    L1CredSUM decimal(19,2),
    L2DebSUM decimal(19,2),
    L2CredSUM decimal(19,2),
    L3DebSUM decimal(19,2),
    L3CredSUM decimal(19,2),
    totalCred decimal(19,2),
    totalDeb decimal(19,2)
);

INSERT INTO TrialBalTbl
SELECT  
    null, 
    P.PARTY_ID, 
    P.ACCOUNT_ID,
    P.NAME,
    SUM(L.DEBIT)-SUM(L.CREDIT),
    0, 
    l1.L1,
    l1.`name` AS 'L1NAME',
    l2.L2, 
    l2.`name` AS 'L2NAME',
    l3.L3,
    l3.`name` AS 'L3NAME',
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0
FROM 
    PLEDGER AS L INNER JOIN PARTY AS P ON L.party_id = p.party_id
    INNER JOIN level3 AS l3 ON l3.l3 = p.level3
    INNER JOIN level2 AS l2 ON l2.l2 = l3.l2
    INNER JOIN level1 AS l1 ON l1.l1 = l2.l1
WHERE 
    DATE(L.VRDATE) BETWEEN startDate AND endDate
GROUP BY 
    l1.L1, l2.L2, l3.L3, P.PARTY_ID,P.NAME 
HAVING IFNULL(SUM(L.DEBIT),0)-IFNULL(SUM(L.CREDIT),0) >0 
order by 
    P.ACCOUNT_ID ASC;

INSERT INTO TrialBalTbl
SELECT  
    null, 
    P.PARTY_ID, 
    P.ACCOUNT_ID,
    P.NAME,
    0,
    SUM(L.CREDIT)-SUM(L.DEBIT), 
    l1.L1, 
    L1.NAME AS 'L1NAME',
    l2.L2, 
    L2.NAME AS 'L2NAME',
    l3.L3,
    L3.NAME AS 'L3NAME',
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0
FROM 
    PLEDGER AS L INNER JOIN PARTY AS P ON L.party_id = p.party_id
    INNER JOIN level3 AS l3 ON l3.l3 = p.level3
    INNER JOIN level2 AS l2 ON l2.l2 = l3.l2
    INNER JOIN level1 AS l1 ON l1.l1 = l2.l1
WHERE  
    DATE(L.VRDATE) BETWEEN startDate AND endDate
GROUP BY  
    l1.L1, l2.L2, l3.L3, P.PARTY_ID,P.NAME HAVING IFNULL(SUM(L.CREDIT),0)-IFNULL(SUM(L.DEBIT),0) > 0 
ORDER BY 
    P.ACCOUNT_ID ASC;

-- Update the whole level's credit and debit sums

UPDATE TrialBalTbl as tbal 
SET L1DebSUM = (
        SELECT SUM(PLEDGER.DEBIT)-SUM(PLEDGER.CREDIT) 
        FROM 
            PLEDGER INNER JOIN PARTY ON PLEDGER.PARTY_ID = PARTY.PARTY_ID 
                    INNER JOIN Level3 ON level3.l3 = Party.level3 
                    INNER JOIN Level2 ON Level3.l2 = level2.l2 
                    INNER JOIN Level1 on Level1.l1 = level2.l1
        WHERE 
            level1.l1 = tbal.l1 AND DATE(PLEDGER.VRDATE) BETWEEN startDate AND endDate
        GROUP BY  
            level1.L1
        HAVING 
            IFNULL(SUM(PLEDGER.DEBIT),0)-IFNULL(SUM(PLEDGER.CREDIT),0) > 0
);

UPDATE TrialBalTbl as tbal 
SET L1CredSUM = (
        SELECT SUM(PLEDGER.CREDIT) -SUM(PLEDGER.DEBIT)
        FROM 
            PLEDGER INNER JOIN PARTY ON PLEDGER.PARTY_ID = PARTY.PARTY_ID 
                    INNER JOIN Level3 ON level3.l3 = Party.level3 
                    INNER JOIN Level2 ON Level3.l2 = level2.l2 
                    INNER JOIN Level1 on Level1.l1 = level2.l1
        WHERE 
            level1.l1 = tbal.l1 AND DATE(PLEDGER.VRDATE) BETWEEN startDate AND endDate
        GROUP BY  
            level1.L1
        HAVING 
            IFNULL(SUM(PLEDGER.CREDIT),0)-IFNULL(SUM(PLEDGER.DEBIT),0) > 0
);

UPDATE TrialBalTbl as tbal 
SET L2DebSUM = (
        SELECT SUM(PLEDGER.DEBIT)-SUM(PLEDGER.CREDIT) 
        FROM 
            PLEDGER INNER JOIN PARTY ON PLEDGER.PARTY_ID = PARTY.PARTY_ID 
                    INNER JOIN Level3 ON level3.l3 = Party.level3 
                    INNER JOIN Level2 ON Level3.l2 = level2.l2 
                    INNER JOIN Level1 on Level1.l1 = level2.l1
        WHERE 
            level1.l1 = tbal.l1 AND level2.l2 = tbal.l2 AND DATE(PLEDGER.VRDATE) BETWEEN startDate AND endDate
        GROUP BY  
            level1.L1, level2.L2
        HAVING 
            IFNULL(SUM(PLEDGER.DEBIT),0)-IFNULL(SUM(PLEDGER.CREDIT),0) > 0
);

UPDATE TrialBalTbl as tbal 
SET L2CredSUM = (
        SELECT SUM(PLEDGER.CREDIT) -SUM(PLEDGER.DEBIT)
        FROM 
            PLEDGER INNER JOIN PARTY ON PLEDGER.PARTY_ID = PARTY.PARTY_ID 
                    INNER JOIN Level3 ON level3.l3 = Party.level3 
                    INNER JOIN Level2 ON Level3.l2 = level2.l2 
                    INNER JOIN Level1 on Level1.l1 = level2.l1
        WHERE 
            level1.l1 = tbal.l1 AND level2.l2 = tbal.l2 AND DATE(PLEDGER.VRDATE) BETWEEN startDate AND endDate
        GROUP BY  
            level1.L1, level2.L2
        HAVING 
            IFNULL(SUM(PLEDGER.CREDIT),0)-IFNULL(SUM(PLEDGER.DEBIT),0) > 0
);

UPDATE TrialBalTbl as tbal 
SET L3DebSUM = (
        SELECT SUM(PLEDGER.DEBIT)-SUM(PLEDGER.CREDIT) 
        FROM 
            PLEDGER INNER JOIN PARTY ON PLEDGER.PARTY_ID = PARTY.PARTY_ID 
                    INNER JOIN Level3 ON level3.l3 = Party.level3 
                    INNER JOIN Level2 ON Level3.l2 = level2.l2 
                    INNER JOIN Level1 on Level1.l1 = level2.l1
        WHERE 
            level1.l1 = tbal.l1 AND level2.l2 = tbal.l2 AND level3.l3 = tbal.l3 AND DATE(PLEDGER.VRDATE) BETWEEN startDate AND endDate
        GROUP BY  
            level1.L1, level2.L2, level3.L3
        HAVING 
            IFNULL(SUM(PLEDGER.DEBIT),0)-IFNULL(SUM(PLEDGER.CREDIT),0) > 0
);

UPDATE TrialBalTbl as tbal 
SET L3CredSUM = (
        SELECT SUM(PLEDGER.CREDIT) -SUM(PLEDGER.DEBIT)
        FROM 
            PLEDGER INNER JOIN PARTY ON PLEDGER.PARTY_ID = PARTY.PARTY_ID 
                    INNER JOIN Level3 ON level3.l3 = Party.level3 
                    INNER JOIN Level2 ON Level3.l2 = level2.l2 
                    INNER JOIN Level1 on Level1.l1 = level2.l1
        WHERE 
            level1.l1 = tbal.l1 AND level2.l2 = tbal.l2 AND level3.l3 = tbal.l3 AND DATE(PLEDGER.VRDATE) BETWEEN startDate AND endDate
        GROUP BY  
            level1.L1, level2.L2, level3.L3
        HAVING 
            IFNULL(SUM(PLEDGER.CREDIT),0)-IFNULL(SUM(PLEDGER.DEBIT),0) > 0
);
    -- PROBLEM here.
UPDATE TrialBalTbl
SET totalCred = (SELECT SUM(DISTINCT L1CredSUM) FROM TrialBalTbl),
     totalDeb = (SELECT SUM(DISTINCT L1DebSUM) FROM TrialBalTbl);

SELECT * FROM TrialBalTbl ORDER BY ACCOUNT_ID;

END

Answer 1

您可以通过使用变量来做到这一点。

在过程开始时声明两个变量:

DECLARE @CredSUM decimal(19,2);
DECLARE @DebSUM  decimal(19,2);

然后设置它们并使用它们更新临时表:

SET @CredSUM = (SELECT SUM(DISTINCT L1CredSUM) FROM TrialBalTbl);
SET @DebSUM  = (SELECT SUM(DISTINCT L1DebSUM ) FROM TrialBalTbl);

UPDATE TrialBalTbl SET
  totalCred = @CredSUM,
  totalDeb  = @DebSUM
;