获取同一张表的数据

Question

我的表有点像

Name      |DateOfEvent|EventType
----------------------------------
Smith     |10/1/2005  |New
Thomas    |1/1/2002   |Updated
Johnson   |6/1/2002   |New
Smith     |7/1/2008   |Updated
Smith     |7/1/2000   |New

我想返回行，其中事件为“New”，日期位于同名行之前，但 EventType 已更新为较晚的日期。

我的想法是迭代带有名称的每一行的表，但这似乎效率很低。有更好的方法吗？

Answer 1

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

Answer 2

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;

Answer 3

使用：

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)

使用 JOIN（内部或外部）的问题是，如果有多个记录标记为“已更新”且日期早于“新”记录，则会出现重复的 x参考文献。

Answer 4

也许是这样的：

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 但随着表的增长，名称可能不会保持唯一。引入主键和父 ID 来跟踪历史记录可能会效果更好。