代码之家 › 专栏 › 技术社区 › Maestro1024

在同一个表中获取数据

select sql

Maestro1024 · 技术社区 · 15 年前

我的桌子有点像

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

我想返回事件为“新建”且日期在同名行之前的行,但事件类型将更新为更晚的日期。

我的想法是用每一行的名称对表进行迭代,但这似乎效率很低。有更好的方法吗?

4 回复 | 直到 15 年前

Mike M. 15 年前

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

Kel 15 年前

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;

OMG Ponies 15 年前

用途:

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 参考文献。

Saul 15 年前

可能是以下方面的原因:

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