谁能解释为什么当第二个查询给我结果时，第一个 SQL 查询给出 0 [英] Can anyone explain why this first SQL query gives 0 when this second query gives me results

问题描述

我有两个我认为应该等效的 SQL 查询:

I have two SQL queries that I thought should be equivalent:

SELECT COUNT(*) FROM (
  SELECT distinct(e.id) FROM entity as e 
  join organization_feature as o on o.entity_id = e.id 
  where exists (
    select * from entity where o.feature_id = 2086 and o.string_value is not null
  ) and ( o.feature_id = 2038 ) GROUP BY e.id 
) as res

这是第一个，这是第二个:

This is the first one and here is the second:

SELECT COUNT(*) FROM (
  SELECT distinct(e.id) FROM entity as e 
  join organization_feature as o on o.entity_id = e.id 
  where ( o.feature_id = 2038 ) 
  or (o.feature_id = 2086 and o.string_value is not null)
  GROUP BY e.id having count(*)=2
) as res

问题是第一个给我 0 作为计数结果，而第二个给我 13411.有关我的数据库结构的更多信息或更好地理解查询 参见此处(如果人们希望我在此处重新发布信息，我很乐意).

The problem is that the first gives me 0 as the count results while the second one gives me 13411. For more information on the structure of my database or to better understand the queries see here (if people would like me to repost the information here I'm happy to).

谁能解释为什么它们不等价，并提供一个我可以使用的whereexists"子句?

Can anyone explain why they are not equivalent and provide a "where exists" clause that I will be able to use?

感谢大家的帮助，感谢您的建议，我意识到我应该使用:

Thank you everyone for your help, thanks to your advice I realised that I should be using:

SELECT COUNT(*) FROM (
  SELECT distinct(e.id) FROM entity as e 
  join organization_feature as o on o.entity_id = e.id 
  where exists (
    select * from organization_feature as of where of.feature_id = 2086 and of.string_value is not null and of.entity_id = e.id
  ) and ( o.feature_id = 2038 ) GROUP BY e.id 
) as res

完成我想做的事情.因为我需要加入第三个变量 of 来完成我正在尝试的查询，这提供了一个与我正在尝试的 query_2 相同的解决方案.再次感谢大家.

to accomplish what I was trying to do. as I needed a third variable of to be joined to accomplish the query I was attempting, this provides a solution that is identical to query_2 as I was trying. Thank you again everyone.

推荐答案

在您提到的另一篇文章中，如果您查看带有 whereexists 子句的答案，您会在此子句中看到在 where exists 子句中的表和主连接中的至少一个表之间进行了连接 - 您在第一个查询中没有这样做.根据您发布的查询，第二个查询应该会产生您感兴趣的结果.

In the other post you are mentioning, if you look at the answer with the where exists clause you would see that in this clause a join was made between the table in your where exists clause and at least one of the tables in your main join - which you haven't done so in your first query. Based on the queries you posted, the second one should yield you the results that you are interested in.

这篇关于谁能解释为什么当第二个查询给我结果时，第一个 SQL 查询给出 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！