将存在和不存在合并到一个查询中 - oracle magic

发布于 2024-09-04 07:58:30 字数 319 浏览 4 评论 0原文

在查询的 WHERE 部分中，我们

SELECT * FROM SomeTable st
WHERE 
NOT EXISTS 
  (SELECT 1 FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)
OR EXISTS 
  (SELECT 1 FROM Tab t2 WHERE (t2.A = st.A OR t2.B = st.A) AND t2.C IS NULL)

似乎是合并的一个很好的候选者...... 但我盯着它看了一个小时，却毫无头绪。

你有什么想法吗？

谢谢，

原文

In a WHERE part of query we have

SELECT * FROM SomeTable st
WHERE 
NOT EXISTS 
  (SELECT 1 FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)
OR EXISTS 
  (SELECT 1 FROM Tab t2 WHERE (t2.A = st.A OR t2.B = st.A) AND t2.C IS NULL)

Seems like a good candidate for merging...
But I'm staring on that for an hour without any idea.

Would you have some thoughts?

Thanks,

分享到QQ

分享到微博

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

发布评论

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

不及他 2024-09-11 07:58:30

SELECT distinct st.* 
FROM SomeTable st 
     left outer join Tab t
     on st.a in (t.a,t.b)
WHERE t.c is null

有时，最简单的答案是使用联接而不是存在。

SELECT distinct st.* 
FROM SomeTable st 
     left outer join Tab t
     on st.a in (t.a,t.b)
WHERE t.c is null

Sometimes the simplest answer is to use a join rather than an exists.

回复收藏 0 原文

甜警司 2024-09-11 07:58:30

尝试：

NOT EXISTS (SELECT 1 FROM Tab WHERE (Tab.A = @Id OR Tab.B = @ID)
            AND (Tab.C is null OR Tab.C != @Var))

Try:

NOT EXISTS (SELECT 1 FROM Tab WHERE (Tab.A = @Id OR Tab.B = @ID)
            AND (Tab.C is null OR Tab.C != @Var))

回复收藏 0 原文

雪若未夕 2024-09-11 07:58:30

我想我已经明白了，

SELECT * FROM SomeTable st
WHERE 
0 = (SELECT SUM (NVL(t1.C,0) FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)

你觉得怎么样？
当然，这只适用于“C”预计为 NULL 的情况。
如果我们期望有一些价值，我认为支票

@SomeValue IN (SELECT t1.C FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)

可以吗？

I think I've got it

SELECT * FROM SomeTable st
WHERE 
0 = (SELECT SUM (NVL(t1.C,0) FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)

What do you think?
Of course that will work only for case where 'C' is expected to be NULL.
If we are expecting some value, I think that the check

@SomeValue IN (SELECT t1.C FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A)

Will do?

回复收藏 0 原文

如日中天 2024-09-11 07:58:30

它看起来好像您正在尝试返回所有 SomeTable 值，其中 Tab 上没有相应的值，其中 C 不为空 - 在这种情况下，以下内容应该有效：

SELECT * FROM SomeTable st
WHERE NOT EXISTS 
  (SELECT 1 FROM Tab t1 WHERE (t1.A = st.A OR t1.B = st.A) AND t2.C IS NOT NULL)

但是，这并不完全正确你现有的查询做了什么 - 在你的查询中，如果 Tab 上有两条对应的记录，其中一条记录的 C 为非空值，另一条为空，你的查询将返回相应的行，但是我的查询不会。

It looks as though you're trying to return all SomeTable values where there's no corresponding value on Tab where C is not null - in which case, the following should work:

SELECT * FROM SomeTable st
WHERE NOT EXISTS 
  (SELECT 1 FROM Tab t1 WHERE (t1.A = st.A OR t1.B = st.A) AND t2.C IS NOT NULL)

However, that's not exactly what your existing query does - in your query, if there are two corresponding records on Tab, where one record has a non-null value for C and the other is null, your query will return a corresponding row, but my query won't.

回复收藏 0 原文

~没有更多了~