我在postgresql上遇到了横向查询问题。我在网上没有找到任何解决方案。你能帮帮我吗?
我的查询示例如下:https://dbfiddle.uk/?rdbms=postgres_10&fiddle=665e3efb0c8229718cf098cf4873df91
我的问题是:
SELECT
bonuses.bonus_id
FROM bonuses,
LATERAL jsonb_array_elements(bonuses.rules) getRules(obj),
LATERAL jsonb_array_elements_text(obj -> 'rule' -> 'providers') rule_providers(id),
LATERAL jsonb_array_elements_text(obj -> 'rule' -> 'games') rule_games(id)
INNER JOIN provider_games ON provider_games.provider_id = rule_providers.id::bigint
or provider_games.provider_game_id = rule_games.id::bigint我得到了下面的错误:
[42P01] ERROR: invalid reference to FROM-clause entry for table "rule_providers" Hint: There is an entry for table "rule_providers", but it cannot be referenced from this part of the query. Position: 335当我使用下面的查询时,一切都是正常的。它起作用了。当我使用多个侧向时,它会爆炸。
SELECT
bonuses.bonus_id
FROM bonuses,
LATERAL jsonb_array_elements(bonuses.rules) getRules(obj),
LATERAL jsonb_array_elements_text(obj -> 'rule' -> 'providers') rule_providers(id)
INNER JOIN provider_games ON provider_games.provider_id = rule_providers.id::bigint我的表结构如下:
create table provider_games (
provider_game_id bigserial not null
constraint provider_games_pkey primary key
);
create table bonuses (
bonus_id bigserial not null
constraint bonuses_pkey primary key,
rules jsonb
);
INSERT INTO bonuses (rules) VALUES ('[{"rule":{"games":["9179","7821","7915"]}},{"rule":{"providers":["19","11"]}}]');
INSERT INTO provider_games (provider_game_id, provider_id) VALUES (9179, 19);
INSERT INTO provider_games (provider_game_id, provider_id) VALUES (7915, 11);
TABLE bonuses;
TABLE provider_games;发布于 2020-09-17 21:06:02
您的代码混合了隐式连接和显式连接。这不是一个好的做法:他们有不同的预见性规则,这会导致像你现在这样的微妙问题。
不要混用连接风格--要保持一致。事实上,总是使用标准的显式连接:
SELECT bonuses.bonus_id
FROM bonuses
CROSS JOIN LATERAL jsonb_array_elements(bonuses.rules) getRules(obj)
CROSS JOIN LATERAL jsonb_array_elements_text(obj -> 'rule' -> 'providers') rule_providers(id)
CROSS JOIN LATERAL jsonb_array_elements_text(obj -> 'rule' -> 'games') rule_games(id)
INNER JOIN provider_games
ON provider_games.provider_id = rule_providers.id::bigint
OR provider_games.provider_game_id = rule_games.id::bigint发布于 2020-09-18 00:44:56
当我做了一些修改后,我得到了正确的结果。谢谢@GMB
SELECT
bonus_id
FROM bonuses,
LATERAL jsonb_array_elements(bonuses.rules) getProviderRules(games),
LATERAL jsonb_array_elements(bonuses.rules) getGamesRules(providers)
CROSS JOIN jsonb_array_elements_text(providers -> 'rule' -> 'providers') rule_providers(id)
CROSS JOIN jsonb_array_elements_text(games -> 'rule' -> 'games') rule_games(id)
INNER JOIN provider_games
ON provider_games.provider_id = rule_providers.id::bigint
OR provider_games.provider_game_id = rule_games.id::bigint
GROUP BY bonus_idhttps://stackoverflow.com/questions/63938667
复制相似问题