检查值是否在其他表中不存在

Question

你好，我有以下问题。

我有这个ER图

​

我需要选择所有在发表PhD论文的年份内没有发表期刊或会议论文的人(PhD是论文中的类型字段)。我的问题是，当我只检查他是否没有发表论文时，我做到了，但当我两者都有时，我失败了，我哪里错了？

Select count(akey)
from persons 
WHERE (Select theses.year 
       from persons 
          inner join theses on persons.akey = theses.akey 
       Where theses.type = 'PhD') not in ((Select year 
           from persons 
             inner join authpapers on persons.akey = authpapers.akey 
             inner join papers on authpapers.pkey = papers.pkey 
             inner join journals on journals.jkey = papers.jkey) 
 and (Select year 
      from persons 
        inner join authpapers on persons.akey = authpapers.akey 
        inner join papers on authpapers.pkey = papers.pkey 
        inner join conferences on conferences.ckey = papers.ckey));

它说从not in开始的部分不是boolean类型，但是我应该怎么做呢？

Answer 1

您的NOT IN子句基本上简化为NOT IN (queryResultJournals AND queryResultPapers)。它试图对两个查询结果进行AND运算，而这并不是您想要的结果(这也是为什么您会得到一个not a boolean错误)。您应该用UNION或UNION ALL替换and，这会将两个查询结果合并为一个，然后在NOT in子句中起作用。

Select count(akey)
from persons 
WHERE (Select theses.year 
       from persons 
          inner join theses on persons.akey = theses.akey 
       Where theses.type = 'PhD') not in ((Select year 
           from persons 
             inner join authpapers on persons.akey = authpapers.akey 
             inner join papers on authpapers.pkey = papers.pkey 
             inner join journals on journals.jkey = papers.jkey) 
 UNION (Select year 
      from persons 
        inner join authpapers on persons.akey = authpapers.akey 
        inner join papers on authpapers.pkey = papers.pkey 
        inner join conferences on conferences.ckey = papers.ckey));

Answer 2

只是为了展示另一种选择:你可以收集所有的出版物(论文，会议和期刊)，然后聚合起来，找到在一年内发表论文加上会议或期刊的人。

with published as
(
  select akey, type, year from theses where type = 'PhD'
  union all
  select ap.akey, 'conference', c.year
  from authpapers ap 
  join papers p on p.pkey = ap.pkey
  join conferences c on c.ckey = p.ckey
  union all
  select ap.akey, 'journal', j.year
  from authpapers ap 
  join papers p on p.pkey = ap.pkey
  join journals j on c.jkey = p.jkey
)
select *
from persons
where akey in
(
  select akey
  from published
  group by akey, year
  having bool_or(type = 'PhD')
  and not ( bool_or(type = 'conference') or bool_or(type = 'journal') )
)
order by akey;

聚合bool_or可以读取为“至少一个”或“任何”。

我们不知道您的数据库是否允许同时作为期刊和会议发表相同的论文。或者一个人是否可以有多个PhD论文(可能是多次尝试？)。上面的查询查看了一个人发表的所有年份，如果它发现一个人至少有一年发表了PhD论文和期刊或会议，它就会显示这个人。

Answer 3

如果可以使用exists()，就不需要计算了

SELECT *
from persons pe -- I need to select all the persons
WHERE NOT EXISTS (SELECT SELECT * -- that did not
       FROM theses th 
        JOIN authpapers ap on pe.akey = ap.akey
        JOIN papers pa on ap.pkey = pa.pkey 
        JOIN journals jo on jo.jkey = pa.jkey -- publish a journal paper
        WHERE th.type = 'PhD'
        AND th.akey = pe.akey
        AND th.year = jo.year  -- in the same year
        )
 AND NOT EXISTS (SELECT *
        FROM theses th
        JOIN authpapers ap on pe.akey = ap.akey
        JOIN papers pa on ap.pkey = pa.pkey
        JOIN conferences co on co.ckey = pa.ckey  -- or publish a conference paper
        WHERE th.type = 'PhD'
        AND th.akey = pe.akey
        AND th.year = co.year -- in the same year
        );