postgresql SQL筛选器查询挂起,无响应

o4tp2gmn  于 2023-03-22  发布在  PostgreSQL
关注(0)|答案(3)|浏览(153)
SELECT users.id 
FROM orders, users, "userEmailCollections", "reviewTracks", "loyaltyCampaignRedemptions" 
WHERE (orders."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6) 
AND users.id = orders."orderUserId") 
OR ("userEmailCollections"."restaurantId" = 6 
AND   "userEmailCollections"."userId" = users.id) 
OR ("reviewTracks"."restaurantId" = 6 
AND users.email = "reviewTracks"."email") 
OR ("loyaltyCampaignRedemptions"."restaurantId" = 6 
AND "users".id = "loyaltyCampaignRedemptions"."userId");

我尝试运行上面的查询,以获取与任何其他表有关系的用户的所有用户ID-例如,如果用户有orderuserEmailCollection,我希望他们的ID显示在查询输出中。
但是,这个查询挂起而没有产生响应,我不知道为什么。

qojgxg4l

qojgxg4l1#

select u.id
from users u
join orders ord on u.id=ord."orderUserId"
where ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6) 
union
select u.id
from users u
join "userEmailCollections" col on u.id=col."userId"
where col."restaurantId" = 6 
union 
select u.id
from users u
join "reviewTracks" rev on u.email=rev."email"
where rev."restaurantId" = 6 
union 
select u.id
from users u
join "loyaltyCampaignRedemptions" loyy on u.id=loyy."userId"
where loyy."restaurantId" = 6

我会说查询可以看起来像这样

b5lpy0ml

b5lpy0ml2#

没有理由连接表,因为查询是关于关联的存在而不是关联的属性。下面的查询应该在合理的时间内返回所需的结果:

SELECT users.id
  FROM users
 WHERE users.id IN
         (SELECT DISTINCT orders."orderUserId"
           FROM orders
          WHERE orders."restaurantTableId" IN (12, 7, 9, 8, 10, 11, 14, 99, 100, 6))
    OR users.id IN
         (SELECT DISTINCT "userEmailCollections"."userId"
            FROM "userEmailCollections"
           WHERE "userEmailCollections"."restaurantId" = 6)
    OR users.email IN
         (SELECT DISTINCT "reviewTracks"."email"
            FROM "reviewTracks"
           WHERE "reviewTracks"."restaurantId" = 6)
    OR users.id IN
         (SELECT DISTINCT "loyaltyCampaignRedemptions"."userId"
            FROM "loyaltyCampaignRedemptions"
           WHERE "loyaltyCampaignRedemptions"."restaurantId" = 6);

DISTINCT包含在子查询中,以便在关联表中存在大量重复的用户ID或电子邮件时提高性能。由于这些是包含测试,因此不使用DISTINCT不会更改结果集。

oknwwptz

oknwwptz3#

回应上面的评论....在join语法中显式,并将查询分解为更小的子查询。这将有助于数据库生成更有效的执行计划。例如:

SELECT DISTINCT users.id
FROM users
LEFT JOIN orders ON users.id = orders."orderUserId" AND orders."restaurantTableId" IN (12, 7, 9, 8, 10, 11, 14, 99, 100, 6)
LEFT JOIN "userEmailCollections" ON "userEmailCollections"."userId" = users.id AND "userEmailCollections"."restaurantId" = 6
LEFT JOIN "reviewTracks" ON users.email = "reviewTracks"."email" AND "reviewTracks"."restaurantId" = 6
LEFT JOIN "loyaltyCampaignRedemptions" ON "users".id = "loyaltyCampaignRedemptions"."userId" AND "loyaltyCampaignRedemptions"."restaurantId" = 6;
WHERE orders."orderUserId" IS NOT NULL OR "userEmailCollections"."userId" IS NOT NULL OR "reviewTracks"."email" IS NOT NULL OR "loyaltyCampaignRedemptions"."userId" IS NOT NULL;

这里的each cte连接两个表,并在特定条件下过滤,而不是笛卡尔连接。使用distinct将有助于从结果集中删除重复的行。它也更容易阅读和理解。

相关问题