在同一个表中获取数据

CREATE TABLE #TEST
(
    Name varchar(10),
    DateOfEvent date,
    EventType varchar(10)
)

INSERT INTO #TEST (Name, DateOfEvent, EventType)
SELECT 'Smith',     '10/1/2005',  'New' UNION ALL
SELECT 'Thomas',    '1/1/2002',   'Updated' UNION ALL
SELECT 'Johnson',   '6/1/2002',   'New' UNION ALL
SELECT 'Smith',     '7/1/2008',   'Updated' UNION ALL
SELECT 'Smith',     '7/1/2000',   'New'

SELECT NEW.* FROM 
    (SELECT Name, MAX(DateOfEvent) AS DateOfEvent
        FROM #TEST
        WHERE EventType = 'Updated'
        GROUP BY Name
    ) UPDATED
    JOIN 
    (SELECT Name, MIN(DateOfEvent) AS DateOfEvent
        FROM #TEST
        WHERE EventType = 'New'
        GROUP BY Name
    )New ON New.DateOfEvent < UPDATED.DateOfEvent AND New.Name = UPDATED.Name

SELECT
  e1.*
FROM
  Events e1,
  Events e2
WHERE
  e1.EventType = 'New' and
  e1.Name = e2.Name and
  e2.EventType = 'Updated' and
  e2.DateOfEvent > e1.DateOfEvent;

或与join相同:

SELECT
  e1.*
FROM
  Events e1
INNER JOIN
  Events e2
ON
  e1.Name = e2.Name
WHERE
  e1.EventType = 'New' and
  e2.EventType = 'Updated' and
  e2.DateOfEvent > e1.DateOfEvent;

用途:

SELECT x.name, 
       x.dateofevent,
       x.eventtype
  FROM YOUR_TABLE x
 WHERE x.eventtype = 'New'
   AND EXISTS(SELECT NULL
                FROM YOUR_TABLE y
               WHERE y.name = x.name
                 AND y.eventtype = 'Updated'
                 AND y.dateofevent > x.dateofevent)

使用联接(内部或外部)的问题是,如果有多个记录标记为“更新”,且日期在“新”记录之前,则 x 参考文献。

可能是以下方面的原因:

SELECT
   t1.Name,
   t1.DateOfEvent,
   t1.EventType
FROM table t1
LEFT JOIN table t2
   t1.Name = t2.Name
WHERE
t1.EventType = 'New' AND t1.DateOfEvent < t2.DateOfEvent AND t2.EventType = 'Updated'

此查询使用 Name JOIN