gpt4 book ai didi

mysql - 如何在同一查询中多次引用 TEMPORARY 表?

转载 作者:可可西里 更新时间:2023-11-01 07:05:35 31 4
gpt4 key购买 nike

MySQL 文档说:“您不能在同一个查询中多次引用一个临时表。”

我知道之前有人问过这个问题。但我找不到针对以下内容的具体解决方案。

我正在对临时表进行预选

CREATE TEMPORARY TABLE preselection AS SELECT ...;

现在我想做一些(大约 20 个甚至 30 个)工会

(SELECT FROM preselection ...)
UNION
(SELECT FROM preselection ...)
UNION
......
UNION
(SELECT FROM preselection ...)

我可以制作 20 或 30 个预选副本,并在每个表上进行每个选择,但如果我理解正确,这与在 UNION 链内的每个 SELECT 中调用上面的预选查询作为子查询相同。

有没有办法解决这个问题?

问候,

克里斯

完整查询:

CREATE TEMPORARY TABLE preselection AS
(
SELECT id, title, chapter, date2, date, snid, max(score) FROM `movies`

WHERE
(
cluster is not NULL
)
AND
(
`date` <= '2012-02-20 05:20:00'
AND `date` > '2012-02-19 17:20:00'
AND (TIMEDIFF(date, date2) < '12:00:00')
)
GROUP BY cluster
)
UNION
(
SELECT id, title, chapter, date2, date, snid, score FROM `movies`
WHERE cluster IS NULL
AND
(
`date` <= '2012-02-20 05:20:00' AND `date` > '2012-02-19 17:20:00' AND (TIMEDIFF(date, date2) < '12:00:00')
)
);

(SELECT * FROM preselection WHERE snid=1 AND chapter LIKE '#A_OT%'
DESC LIMIT 4)
UNION

UNION
(SELECT * FROM preselection WHERE snid=19 AND chapter LIKE '#A_OT%'
LIMIT 4)
UNION
... for each chapter from A to J and every snid from 1 to 19 ...
UNION
(SELECT * FROM preselection WHERE snid=1 AND chapter LIKE '#J_OT%'
LIMIT 4)
UNION

UNION
(SELECT * FROM preselection WHERE snid=19 AND chapter LIKE '#J_OT%'
LIMIT 4)

ORDER BY `score` DESC, `date`;

最佳答案

我认为错误信息很明确:你不能用一个临时表来做到这一点。创建数据 View 而不是临时表是否可以解决问题?

Views in mysql

关于mysql - 如何在同一查询中多次引用 TEMPORARY 表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9364602/

31 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com