mysql 这个永久的table怎么能变成临时的呢？

dzjeubhm 于 2023-06-04 发布在 Mysql

关注(0)|答案(1)|浏览(156)

我需要将下面的永久MySQL表临时化，以避免像FINAL_RESULTS这样的表污染DB，我在查询的其他地方使用了这些表，但不想持久化。

CREATE TABLE FINAL_RESULTS(
ID VARCHAR(38),
DATE1 DATETIME,
DATE2 DATETIME,
PRIMARY KEY(ID,DATE1)
);

INSERT INTO FINAL_RESULTS(DATE1,DATE2)
SELECT DISTINCT t2.ID,DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))
FROM SOURCE1 AS t1
INNER JOIN SOURCE2 AS t2
ON t1.ID = t2.ID
WHERE t1.code IN ("a", "b")
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= t2.DATE3
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= t2.DATE4;

UPDATE FINAL_RESULTS
INNER JOIN (
    SELECT t2.PAT_ID, MIN(DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))) AS mindate
    FROM SOURCE1 AS t1
    INNER JOIN FINAL_RESULTS AS t2
    ON t1.ID = t2.ID
    WHERE t1.CODE IN ("c", "d")
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= DATE_SUB(t2.DATE1, INTERVAL 21 DAY)
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= DATE_ADD(t2.DATE1, INTERVAL 14 DAY)
    GROUP BY t1.ID
) AS x ON FINAL_RESULTS.ID = x.ID
SET DATE2=x.mindate;

问题：如果我只是在这里使用“CREATE TEMPORARY TABLE”而不是“CREATE TABLE”，我将得到“Can 't reopen table 'FINAL_RESULTS'”错误，因为MySQL只允许你在每个查询中引用临时表一次，而最后一条语句违反了这一点。文档建议使用通用表表达式来解决这个问题，但我运行的是MySQL 5.7.12，CTE只在8.0版本中工作。

有没有人对5.7.12版本有任何替代解决方案？
先谢谢你了！

mysql

来源：https://stackoverflow.com/questions/76364059/how-can-this-permanent-table-be-made-temporary

1条答案

按热度按时间

6jjcrrmo1#

CREATE TEMPORARY TABLE FINAL_RESULTS(
ID VARCHAR(38),
DATE1 DATETIME,
DATE2 DATETIME,
PRIMARY KEY(ID,DATE1)
);

INSERT INTO FINAL_RESULTS(DATE1,DATE2) -- ??? should be FINAL_RESULTS(ID, DATE1) ?
SELECT DISTINCT t2.ID,DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))
FROM SOURCE1 AS t1
INNER JOIN SOURCE2 AS t2
ON t1.ID = t2.ID
WHERE t1.code IN ("a", "b")
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= t2.DATE3
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= t2.DATE4;

-- convert the subquery to second temptable
CREATE TEMPORARY TABLE x
    SELECT t2.PAT_ID, -- ??? should be `AS id` ?
           MIN(DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))) AS mindate
    FROM SOURCE1 AS t1
    INNER JOIN FINAL_RESULTS AS t2
    ON t1.ID = t2.ID
    WHERE t1.CODE IN ("c", "d")
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= DATE_SUB(t2.DATE1, INTERVAL 21 DAY)
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= DATE_ADD(t2.DATE1, INTERVAL 14 DAY)
    GROUP BY t1.ID;

-- use second tempotable instead of the subquery
UPDATE FINAL_RESULTS
INNER JOIN x ON FINAL_RESULTS.ID = x.ID
SET DATE2=x.mindate;

赞(0）回复(0）举报 2023-06-04

我来回答

mysql 这个永久的table怎么能变成临时的呢？

1条答案

相关问题

热门标签

最新问答