mysql - SQL 包含和排除

Question

属性列表表有一个列表 ID (lid) 和所有者 ID (cid)。

hasA、hasB、hasC 表是属性的描述符。 hasA 包含事件代码，如:待售、出租等 hasB 包含状态代码，如:新价格、刚刚上市、契约(Contract)中等 hasC 包含描述代码，如:单户、土地、农场、海滨、山景、原木等.

我没有预见到在 hasA 表中使用排除，但我确实需要在 hasB 和 hasC 表中使用它。

在这个例子中，我将只引用数字。一切都可以复制到 sqlfiddle。

create table listings (
  lid integer, 
  cid integer 
);

create table hasA (
  lid integer,
  cid integer,
  id integer
 );

create table hasB (
  lid integer,
  cid integer,
  id integer
 );

create table hasC (
  lid integer,
  cid integer,
  id integer
 );


insert into listings values
(901,4),(902,4),(903,4),(904,4),(905,4),(906,4),(907,4),(908,5);

insert into hasA values
(901,4,333),(902,4,333),(903,4,333),(904,4,333),(905,4,333),(906,4,333),(907,4,444),(908,5,333);

insert into hasB values
(901,4,700),(901,4,707),(902,4,702),(902,4,701),(903,4,701),(904,4,708),(905,4,708);

insert into hasC values
(901,4,2000),(901,4,2001),(902,4,2000),(902,4,2003),(903,4,2000),(903,4,2001),(904,4,2015);

我需要帮助构建两个查询。可能这已得到解答，但我确实查看了现有的解决方案，但无法将其他解决方案应用于我的问题。我的 SQL 很基础。

在第一个查询中，我包括了所有在 hasA 中有 333 个，在 hasC 中有 2001 个的列表。使用 IN () 对我的结果一直很有效。结果集:901、903。

select a.lid 
  from listings a, hasA b, hasC c 
 where a.lid = b.lid 
   and a.lid = c.lid 
   and a.cid = b.cid 
   and a.cid = c.cid 
   and b.id in (333) 
   and c.id in (2001);

现在我正在尝试获取 hasC 不能包含 2001 的所有列表。根据视觉推导，所需的结果集是:902、904、905、906。我在下面使用 NOT IN() 不起作用。

select a.lid 
  from listings a, hasA b, hasC c 
 where a.lid = b.lid 
   and a.lid = c.lid 
   and a.cid = b.cid 
   and a.cid = c.cid 
   and b.id in (333) 
   and c.id not in (2001);

下一个示例涉及相同的逻辑，但包括 hasB 和 hasC 表。

直接包含可以正常工作并给出结果集:903

select a.lid 
  from listings a, hasA b, hasB c, hasC d 
 where a.lid = b.lid 
   and a.lid = c.lid 
   and a.lid = d.lid 
   and a.cid = b.cid 
   and a.cid = c.cid 
   and a.cid = d.cid 
   and b.id in (333) 
   and c.id in (701) 
   and d.id in (2001)

现在我正在尝试获取 hasC 不能包含 2001 的所有列表。根据视觉推导，所需的结果集是:902。我在下面使用 NOT IN() 不起作用。

select a.lid 
  from listings a, hasA b, hasB c, hasC d 
 where a.lid = b.lid 
   and a.lid = c.lid 
   and a.lid = d.lid 
   and a.cid = b.cid 
   and a.cid = c.cid 
   and a.cid = d.cid 
   and b.id in (333) 
   and c.id in (701) 
   and d.id not in (2001)

如果我想要的基于视觉演绎的结果集不正确，请更正。

Answer 1

我相信您正在处理“集合中的集合”查询。我喜欢 group by 和 having 来解决这个问题，正是因为它非常灵活。

以下是您上次查询的内容:

select a.lid
from listings a left join
     hasA b
     on a.lid = b.lid left join
     hasB c
     on a.cid = c.cid left join
     hasC d
     on a.lid = d.lid
group by a.lid
having sum(b.id in (333)) > 0 and
       sum(c.id in (701)) > 0 and
       coalesce(sum(d.id in (2001)), 0) = 0;

having 子句中的每个条件都对应于其中一个条件。 > 0 表示在列表中找到了条件。 = 0 表示不是。

SQL Fiddle 是 here .

在您的情况下，因为值来自不同的表，您还可以使用 exists 和 not exists:

select a.*
from listings a
where exists (select 1 from hasA b where a.lid = b.lid and b.id in (333)) and
      exists (select 1 from hasB c where a.cid = c.cid and c.id in (701)) and
      not exists (select 1 from hasC d where a.lid = d.lid and d.id in (2001));

有了适当的索引，这在 MySQL 中可能会更快。