sql查询以提取新记录 [英] sql query to extract new records

问题描述

我有下表:

CREATE TABLE Company (
    CompanyUniqueID BIGSERIAL PRIMARY KEY NOT NULL,
    Name VARCHAR (150) NOT NULL
 );

CREATE TABLE Item ( 
  ItemUniqueID BIGSERIAL PRIMARY KEY NOT NULL,
  CompanyUniqueID BIGINT NULL REFERENCES company DEFERRABLE INITIALLY DEFERRED,
  Name VARCHAR (150) NOT NULL,
  AddedDate TIMESTAMP without time zone DEFAULT now()
);

在应用程序的生命期内，新公司和新项目将添加到表中. 我希望创建一个sql查询，该查询将从给定日期选择新添加的公司" 我从以下查询开始:

In the life time of the application new companies and items are added to the tables. I wish to create an sql query that will select the "new added companies" from a given date I've started with this query:

(Select * from company
 where companyuniqueid in (
   select distinct companyuniqueid from Item where AddedDate > '2014-10-25'))

上述内容不好，因为还会选择2014年10月25日之后添加的，属于已经存在的公司的项目.

The above is not good because items that were added after 2014-10-25 and belong to companies that already exist will be also selected.

例如，2014年10月20日的Company表的快照如下所示:

For example, a snapshot of Company table from 2014-10-20 can look like this:

1 AAA
2 BBB
3 CCC

和表项目看起来像:

1 1 111 2014-10-01
2 2 222 2014-10-10
3 2 333 2014-10-10
4 3 444 2014-10-15

在2014-10-26上添加了以下记录:

on the 2014-10-26 the following records were added:

餐桌公司

4 DDD

表项

5 1 555 2014-10-26
6 3 663 2014-10-26
7 4 777 2014-10-27

我尝试将其添加到查询中:

I've tried adding this to the query:

(Select * from company
 where companyuniqueid in (
    select distinct companyuniqueid from Item
    where AddedDate > '2014-10-25')
 and companyuniqueid not in (
    select distinct companyuniqueid from Item
    where AddedDate <= '2014-10-25'))

但是我得到的结果为空，为了只接收4 DDD，查询应该是什么?

but I'm getting an empty result, what should be the query in order to receive only 4 DDD?

推荐答案

使用

Use an EXISTS anti-semi-join. Typically fastest and cleaner than NOT IN:

SELECT *
FROM   company c
WHERE  NOT EXISTS (
   SELECT 1
   FROM   item
   WHERE  addeddate < '2014-10-25'
   AND    companyuniqueid = c.companyuniqueid);

这将返回
在给定日期之前没有商品的公司.
包括没有项目的公司，并且可能包含具有addeddate IS NULL的项目.
要将结果限制为有新项目的公司，请添加:

This returns
companies that have no items pre-dating a given date.
including companies without items and possibly with items that have addeddate IS NULL.
To limit result to companies with new items, add:

WHERE  EXISTS (
   SELECT 1
   FROM   item
   WHERE  addeddate >= '2014-10-25'
   AND    companyuniqueid = c.companyuniqueid)

但是请考虑在表company中添加另一列added_date，以避免歧义并简化操作.

But consider adding another column added_date to the table company to avoid ambiguities and simplify things.

可能是 NOT IN的另一种情况，其集合包含NULL值.
您的列item.companyuniqueid允许NULL值.您的子查询:

Probably another case of NOT IN with a set containing a NULL value.
Your column item.companyuniqueid allows NULL values. Your subquery:

select distinct companyuniqueid from Item where AddedDate <= '2014-10-25'

...可能包含NULL值.在这种情况下，此表达式永远不会TRUE:

... probably includes a NULL value. In that case, this expression is never TRUE:

companyuniqueid not in (<above subquery>)

如果集合包含NULL值，则返回FALSE或NULL(未知").但是只有TRUE才有资格成为WHERE条件.因此，不会返回任何行.

Returns FALSE or NULL ("unknown") if the set includes a NULL value. But only TRUE would qualify as WHERE condition. So no row is returned.

请注意，对于空集而言，情况并非如此.如果上述子查询将返回无行，则NOT IN只要左侧为NOT NULL，表达式的计算结果将为TRUE.

Note that the same is not true for an empty set. If above subquery would return no row, the NOT IN expression would evaluate to TRUE, as long as the left side is NOT NULL.

基本上，尽量避免使用NOT IN (<subquery>). NOT EXISTS几乎总是优越的.
如果使用它，请了解有关NULL值的方法.详细信息:

Basically, avoid NOT IN (<subquery>) where you can. NOT EXISTS is almost always superior.
If you use it, know your way around NULL values. More details:

• 查找不存在联接的记录
• 选择在其他表格

• Find records where join doesn't exist
• Select rows which are not present in other table

这篇关于sql查询以提取新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！