比较每月记录的条目

Question

我有一个记录每月条目的SQL表。

条目是.csv文本文件的内容。

内容具有多个重复的字段，因此主键是一个复合键，其中包括名称（varchar），Description（varchar）和reportran（dateTime）。记录文件后，它看起来像：

 Name | Description | ReportRan
comp1 | some data   | 2017-01-01
comp1 | more data   | 2017-01-01
comp1 | even more   | 2017-01-01
comp1 | s0me data   | 2017-02-01
comp1 | more data   | 2017-02-01
comp1 | new data    | 2017-02-01

我需要得到一月份而不是2月的行。（Row3）

二月不在一月。（Row6）

和行之间发生了田野变化的行。（Row4）

Answer 1

您可以使用不存在：

月的行= 1个月不存在= 2

select t1.Name, t1.Description, t1.ReportRan
from   your_table t1
where  month(t1.ReportRan) = 1
and    not exists (select 1
                  from your_table t2
                  where t1.Name = t2.Name
                  and   t1.Description = t2.Description
                  and   month(t2.ReportRan) = 2);

或月的行= 2在月份不存在= 1

select t1.Name, t1.Description, t1.ReportRan
from   your_table t1
where  month(t1.ReportRan) = 2
and    not exists (select 1
                  from your_table t2
                  where t1.Name = t2.Name
                  and   t1.Description = t2.Description
                  and   month(t2.ReportRan) = 1);

到目前

select t1.Name, t1.Description, t1.ReportRan
from   @tbl t1
where  month(t1.ReportRan) = 1
and    not exists (select 1
                  from @tbl t2
                  where t1.Name = t2.Name
                  and   t1.Description = t2.Description
                  and   month(t2.ReportRan) = 2)
UNION
select t1.Name, t1.Description, t1.ReportRan
from   @tbl t1
where  month(t1.ReportRan) = 2
and    not exists (select 1
                  from @tbl t2
                  where t1.Name = t2.Name
                  and   t1.Description = t2.Description
                  and   month(t2.ReportRan) = 1);

|Name |Description|ReportRan          |
|:----|:----------|:------------------|
|comp1|some data  |01/01/2017 00:00:00|
|comp1|even more  |01/01/2017 00:00:00|
|Name |Description|ReportRan          |
|:----|:----------|:------------------|
|comp1|s0me data  |01/02/2017 00:00:00|
|comp1|new data   |01/02/2017 00:00:00|
|Name |Description|ReportRan          |
|:----|:----------|:------------------|
|comp1|even more  |01/01/2017 00:00:00|
|comp1|new data   |01/02/2017 00:00:00|
|comp1|s0me data  |01/02/2017 00:00:00|
|comp1|some data  |01/01/2017 00:00:00|

dbfiddle在这里