Oracle如何检查某个特定的id是否存在于多个表中以及哪些表中存在？

发布于 2024-10-09 19:50:40 字数 651 浏览 5 评论 0原文

我有一个名为 persons 的表，其主键是 pid，我有 3 个不同的表（我们称它们为 A、B< /code> 和 C），其中 pid 是外键。

我需要知道这些表中是否存在特定的 pid 以及具体是哪些表。

我不知道这是否可以通过单个查询来实现，但我认为在我的特定情况下，一个函数会更好地工作，这样我就可以这样做：

SELECT pid, name, sex, func_name(pid) my_str
FORM persons
WHERE pid = 5

然后，根据找到 pid 的表，my_str 类似于：

B | C

意思是在表 B 和 C 上找到了 pid = 5。

我可以使用 COUNT(*) 在函数中执行 3 个 SELECT 来完成此操作。如果结果是> 0，然后从相应的 SELECT 中在表中找到 pid = 5。但这似乎是一种愚蠢的方法，有建议吗？

原文

I have a table named persons which the primary key is pid than I have 3 different tables (let's call them A, B and C) where pid is a foreign key.

I need to know if a specific pid exists in any of those tables and exactly which of them.

I don't know if this is achievable with a single query but I'm thinking that a function would work better in my specific case so that I could do this:

SELECT pid, name, sex, func_name(pid) my_str
FORM persons
WHERE pid = 5

And then, depending on the tables that pid is found, my_str would be something like:

B | C

Meaning pid = 5 was found on tables B and C.

I could do this with 3 SELECTs in the function, using COUNT(*). If the result is > 0, then pid = 5 was found on the table from the respective SELECT. But this seems a stupid way to do it, suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

中性美 2024-10-16 19:50:40

像下面这样的东西对你有用吗？我刚刚添加了 case/whens 来向您展示如何测试其他表中是否存在。

select p.pid
      ,p.name
      ,p.sex
      ,case when a.pid = p.pid then 'Yes' else 'No' end as in_a
      ,case when b.pid = p.pid then 'Yes' else 'No' end as in_b
      ,case when c.pid = p.pid then 'Yes' else 'No' end as in_c
  from persons p
  left outer join a on (a.pid = p.pid)
  left outer join b on (b.pid = p.pid)
  left outer join c on (c.pid = p.pid)
 where p.pid = 5;

Would something like the following do the trick for you? I just added case/whens to show you how you could test for existance in the other tables.

select p.pid
      ,p.name
      ,p.sex
      ,case when a.pid = p.pid then 'Yes' else 'No' end as in_a
      ,case when b.pid = p.pid then 'Yes' else 'No' end as in_b
      ,case when c.pid = p.pid then 'Yes' else 'No' end as in_c
  from persons p
  left outer join a on (a.pid = p.pid)
  left outer join b on (b.pid = p.pid)
  left outer join c on (c.pid = p.pid)
 where p.pid = 5;

回复收藏 0 原文

浮云落日 2024-10-16 19:50:40

对 @Ronnis 答案的增强（我没有测试它，但我希望这样的东西会起作用。如果没有，请原谅我迟到了:)）

select p.pid,
       p.name,
       p.sex,
       case when a.pid = p.pid then 'A' else '' end |
       case when b.pid = p.pid then 'B' else '' end |
       case when c.pid = p.pid then 'C' else '' end
  from persons p
  left outer join a on (a.pid = p.pid)
  left outer join b on (b.pid = p.pid)
  left outer join c on (c.pid = p.pid);

An enhancement to @Ronnis 's answer (I didn't test it, but I hope something like this will work. If not, forgive me for the late hour :) )

select p.pid,
       p.name,
       p.sex,
       case when a.pid = p.pid then 'A' else '' end |
       case when b.pid = p.pid then 'B' else '' end |
       case when c.pid = p.pid then 'C' else '' end
  from persons p
  left outer join a on (a.pid = p.pid)
  left outer join b on (b.pid = p.pid)
  left outer join c on (c.pid = p.pid);

回复收藏 0 原文

~没有更多了~