主页 / 问题 / 1045931
Accepted
Al Mr
Asked:2020-11-14 15:58:47 +0000 UTC2020-11-14 15:58:47 +0000 UTC

帮我写一个 sql 查询来使用连接表获取记录

  • 772

有2张桌子。

表post：（ID、标题、描述）

表post_for_users：（id，user_id，post_id）

一些post帖子post_for_users与用户 ID 相关联。

post如果用户 id 存在于相关表中，则需要从表中获取所有记录post_for_user。一切看起来都很简单，但只有当来自的帖子post中没有单个条目post_for_user时，该帖子也应该显示在最终选择中。

也就是说，如果帖子在 中没有关联的条目post_for_user，或者如果存在具有特定的已知 user_id 的条目，则显示该条目

我不擅长复杂的 sql 查询，我尝试了这样的事情（当然我得到了我需要的错误结果）：

SELECT * FROM post LEFT JOIN post_for_user ON post.id = post_for_user.post_id WHERE post_for_user.user_id = {ID ПОЛЬЗОВАТЕЛЯ} OR COUNT(post_for_user) = 0

sql

2 个回答

  1. Best Answer

    使用联合：结合两个查询 - 按用户过滤的帖子和没有用户的帖子。

    像这样的东西：

    SELECT p.* 
FROM post p 
WHERE EXISTS ( SELECT * 
               FROM post_for_user pu 
               WHERE pu.post_id = p.Id 
                 AND pu.user_Id = {ID ПОЛЬЗОВАТЕЛЯ} )
UNION
SELECT p.* 
FROM post p 
WHERE NOT EXISTS ( SELECT * 
                   FROM post_for_user pu 
                   WHERE pu.post_id = p.Id )
    • 2

  2. 任务不清楚，但解决方案大概是这样的：

    with post as (
  select 1 as ID, 'title1' as TITLE, 'descr1' as DESCRIPTION from dual union all
  select 2 as ID, 'title2' as TITLE, 'descr2' as DESCRIPTION from dual union all
  select 3 as ID, 'title3' as TITLE, 'descr3' as DESCRIPTION from dual
)
  , post_for_users as (
  select 1 as ID, 1 as USER_ID, 1 as POST_ID from dual union all
  select 2 as ID, 2 as USER_ID, 2 as POST_ID from dual
)
/*
select *
from post
  left join post_for_users pfu on post.id = pfu.post_id
where pfu.user_id = :P1 or pfu.user_id is Null
*/
/*
select *
from post, post_for_users pfu
where post.id = pfu.post_id(+)
  and (pfu.user_id = :P1 or pfu.user_id is Null)
*/
    • 2

相关问题