我有一个在mysql workbench中运行得很好的查询,但是当我尝试在datastudio中的mysql connector中运行它时,却遇到了一个与datastudio相关的错误。我试着在谷歌上四处搜索,并在so和datastudio的留言板上询问一些想法,但没有得到多少想法。我发现的大多数回答都与java有关。
我的问题:
CREATE TEMPORARY TABLE cte_most_recent_record
SELECT
leeds_new.leenk_ladder_history.member_id as member_id,
max(leeds_new.leenk_ladder_history.date_trigger_event) AS date_trigger_event_max
FROM leeds_new.leenk_ladder_history
WHERE leeds_new.leenk_ladder_history.ladder_change = 1 and leeds_new.leenk_ladder_history.ladder_advocacy is not NULL
group by leeds_new.leenk_ladder_history.member_id;
CREATE TEMPORARY TABLE cte_most_recent_record_date_ladder
select
lh.member_id,
lh.ladder_advocacy,
lh.date_trigger_event
from leeds_new.leenk_ladder_history as lh
inner join
cte_most_recent_record as cte_rr on lh.member_id = cte_rr.member_id
and lh.date_trigger_event = cte_rr.date_trigger_event_max
limit 100;
CREATE TEMPORARY TABLE cte_ladder_counts_before
select
ladder_advocacy,
count(ladder_advocacy) as ladder_counts_before
from cte_most_recent_record_date_ladder
where date_trigger_event < date('2018-01-01')
group by ladder_advocacy;
CREATE TEMPORARY TABLE cte_ladder_counts_after
select
ladder_advocacy,
count(ladder_advocacy) as ladder_counts_after
from cte_most_recent_record_date_ladder
where date_trigger_event > date('2018-01-01')
group by ladder_advocacy;
select
cte_ladder_counts_before.ladder_advocacy,
cte_ladder_counts_before.ladder_counts_before,
ladder_counts_after,
ladder_counts_before - ladder_counts_after
from cte_ladder_counts_before
inner join cte_ladder_counts_after on cte_ladder_counts_before.ladder_advocacy = cte_ladder_counts_after.ladder_advocacy;
drop table cte_most_recent_record;
drop table cte_most_recent_record_date_ladder;
drop table cte_ladder_counts_before;
drop table cte_ladder_counts_after;
错误:
Sorry, we encountered an error and were unable to complete your request.
Failed to execute connection with error: Can not issue data manipulation statements with executeQuery().
Error ID: 24721465
我相信这与我在同一个查询中有多个表有关-因为我可以用一个select语句运行查询?但我不确定是否还有其他我应该注意的datastudio事情。
思想?
1条答案
按热度按时间djp7away1#
我知道现在回答有点晚,但以防万一将来有人需要这个。我今天碰到了同样的问题。答案是相当令人沮丧的,因为它很容易解决。
你用的是
CREATE TEMPORARY TABLE
句子(因为我也在做)。然后我意识到datastudio不能处理这个句子(显然,它也不能进行多重查询)。所以我的解决方法是在数据库中实际创建表,而不是作为临时表(有效但一点也不干净)。。。并删除
CREATE TEMPORARY TABLE
我的问题中的句子。工作起来很有魅力,但在黑暗的一面,现在我有一个无用的表在我的数据库只是为了处理查询。希望有帮助,亲切的问候。