如何在sql server 2008 r2中获得不匹配的记录 [英] how to get unmatched records in sql server 2008 r2

问题描述

大家好，



我有桌子，



col

Hi guys,

I have a table,

col

SELECT [RecID]
      ,[RegNo]
      ,[BusNo]
      ,[Status]
      ,[Mode]
      ,[InsertBy]
      ,[InsertionDate]
  FROM DailyRecords

用户每天都会插入记录。



我必须比较任何两天的记录，并且只能获取前一天不存在的记录（当天-1）。



假设在第1天我有10条记录，在第2天我有12条记录，

前10条记录匹配但不是11& 12，

现在我只需要从第2天获取11和12条记录。（我的意思是无与伦比的记录）。



任何人都可以请帮助我...





谢谢

the user will insert records daily.

I have to compare any two days records and have to fetch only those records which are not present in previous day(Current day -1).

Suppose in day 1 i have 10 records and in day 2 i have 12 records,
top 10 records are matching but not 11 & 12,
now just i have to fetch, 11&12 records from day2.(i mean unmatched records).

Can anyone please help me...


Thanks

推荐答案

'IS NULL 帮助我获得无与伦比的记录。

'IS NULL' helps me to get unmatched records.

DECLARE  @month int, @yr int
DECLARE @tbl TABLE(RecID int, RegNo nvarchar(50), BusNo nvarchar(50), [Status] nvarchar(50), Mode nvarchar(50), InsertBy int, InsertionDate DATE)

SET @month = 9
SET @yr = 2014

INSERT INTO @tbl(RecID, RegNo, BusNo, [Status], Mode, InsertBy, InsertionDate)
SELECT [RecID]
      ,[RegNo]
      ,[BusNo]
      ,[Status]
      ,[Mode]
      ,[InsertBy]
      ,[InsertionDate]
  FROM dailyrecords
  WHERE MONTH(InsertionDate) = @month and YEAR(InsertionDate) = @yr

SELECT
       g.[RecID]
      ,g.[RegNo]
      ,g.[BusNo]
      ,g.[Status]
      ,g.[Mode]
      ,g.[InsertBy]
      ,g.[InsertionDate]
      FROM @tbl t
right join  dailyrecord g on g.RegNo = t.RegNo
WHERE g.InsertionDate = '10/25/2014' and g.BusNo= 1 and g.[Status] = 'yes' and t.RegNo is null

还有其他方式

There is other way also

SELECT [RecID]
    ,[RegNo]
    ,[BusNo]
    ,[Status]
    ,[Mode]
FROM dailyrecords
WHERE InsertionDate=@currentDate
  Except
  (
    SELECT [RecID]
          ,[RegNo]
          ,[BusNo]
          ,[Status]
          ,[Mode]
    FROM dailyrecords
    WHERE InsertionDate=@currentDate-1
  )

这篇关于如何在sql server 2008 r2中获得不匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！