需要帮助创建复杂的T-SQL SELECT语句

I am trying to make SELECT statement for following situation and need help to make this SELECT statement. It's SQL Server 2005.

我正在尝试为以下情况创建SELECT语句,并需要帮助来生成此SELECT语句。这是SQL Server 2005。

When the select statement is run, it should return rows which have SentDate as NULL assuming that there are no duplicate PersonID in table. It will return result set with Status as 'Initial Record' (as the same person id was not sent already). Once data is exported, SentDate will have today's date.

当运行select语句时,它应该返回具有SentDate为NULL的行,假设表中没有重复的PersonID。它将返回结果集,其状态为“初始记录”(因为尚未发送相同的人员ID)。导出数据后,SentDate将具有今天的日期。

Now assume that two new row have been added and PersonID is duplicate. So when statement is run, it should return only newly added two records but status as 'Follow up Record' because same Person was exported in previous export job.

现在假设添加了两个新行,PersonID是重复的。因此,当语句运行时,它应该只返回新添加的两个记录,但状态为“跟进记录”,因为在先前的导出作业中导出了相同的人员。

Here is the example of what is expected:

以下是预期的示例:

The initial table state would be:

初始表状态为:

RecordId  PersonId  SentDate  CreatedDate
1  1  NULL  8/8/2010
2  2  NULL  8/8/2010
3  3  NULL  8/8/2010

RecordId PersonId SentDate CreatedDate 1 1 NULL 8/8/2010 2 2 NULL 8/8/2010 3 3 NULL 8/8/2010

When first time run, it should return following resultset:

第一次运行时,它应返回以下结果集:

RecordId  PersonId  RecordStatus
1  1  'Initial Record'
2  2  'Initial Record'
3  3  'Initial Record'

RecordId PersonId RecordStatus 1 1'初始记录'2 2'初始记录'3 3'初始记录'

Now the state of the table will be:

现在表的状态将是:

RecordId  PersonId  SentDate  CreatedDate
1  1  8/9/2010  8/8/2010
2  2  8/9/2010  8/8/2010
3  3  8/9/2010  8/8/2010

RecordId PersonId SentDate CreatedDate 1 1 8/9/2010 8/8/2010 2 2 8/9/2010 8/8/2010 3 3 8/9/2010 8/8/2010

Suppose later a new has been added in table and so table values will be:

假设稍后在表中添加了一个新的,因此表值将是:

RecordId  PersonId  SentDate  CreatedDate
1  1  8/9/2010  8/8/2010
2  2  8/9/2010  8/8/2010
3  3  8/9/2010  8/8/2010
4  2  NULL  8/8/2010
5  2  NULL  8/8/2010

RecordId PersonId SentDate CreatedDate 1 1 8/9/2010 8/8/2010 2 2 8/9/2010 8/8/2010 3 3 8/9/2010 8/8/2010 4 2 NULL 8/8/2010 5 2 NULL 2010年8月8日

Now this query should return

现在这个查询应该返回

RecordId  PersonId  RecordStatus
4  2  'Follow up Record'
5  2  'Follow up Record'

RecordId PersonId RecordStatus 4 2'跟进记录'5 2'跟进记录'

2 个解决方案

#1

Assuming your table is named Contacts:

假设您的表名为Contacts:

SELECT RecordId, C1.PersonId, 'Initial Record' AS RecordStatus
FROM Contacts C1
INNER JOIN (SELECT PersonId, COUNT(PersonId) as PersonCount 
            FROM Contacts 
            GROUP BY PersonId) C2 ON
 C1.PersonId = C2.PersonId
WHERE SentDate IS NULL AND PersonCount = 1

UNION

SELECT RecordId, C1.PersonId, 'Follow Up Record' AS RecordStatus
FROM Contacts C1
INNER JOIN (SELECT PersonId, COUNT(PersonId) as PersonCount 
            FROM Contacts 
            GROUP BY PersonId) C2 ON
 C1.PersonId = C2.PersonId
WHERE SentDate IS NULL AND PersonCount > 1

ORDER BY RecordId

The query works by assuming that if a PersonId only exists once in the table, it's an initial record; otherwise, it is a Follow Up Record.

该查询的工作原理是假设如果一个PersonId只在表中存在一次,则它是一个初始记录;否则,它是一个跟进记录。

EDIT

By popular demand, now with 100% less UNION!

受欢迎的需求,现在减少100%的UNION!

SELECT RecordId, C1.PersonId, 
 CASE WHEN PersonCount > 1 THEN 'Follow Up Record' 
 WHEN PersonCount = 1 THEN 'Initial Record' END
 AS RecordStatus
FROM Contacts C1
INNER JOIN (SELECT PersonId, COUNT(PersonId) as PersonCount 
            FROM Contacts
            GROUP BY PersonId) C2 ON
    C1.PersonId = C2.PersonId
WHERE SentDate IS NULL

EDIT 2

It's probably overkill, but here's my first shot at a CTE version:

这可能是矫枉过正,但这是我在CTE版本上的第一次拍摄:

With PersonCountCTE (PersonId, PersonCount) AS 
(
    SELECT PersonId, COUNT(PersonId) AS PersonCount
    FROM Contacts 
    GROUP BY PersonId
)
SELECT RecordId, C1.PersonId, 
    CASE WHEN PersonCount > 1 THEN 'Follow Up Record' 
    WHEN PersonCount = 1 THEN 'Initial Record' END
    AS RecordStatus
FROM Contacts C1 
INNER JOIN PersonCountCTE C2 ON C1.PersonId = C2.PersonId
WHERE SentDate IS NULL 

#2

With Data As
    (
    Select RecordId, PersonId, SentDate, CreatedDate
        , Row_Number() Over ( Partition By PersonId 
                              Order By CreatedDate Desc, RecordId Desc ) As Seq
    From #Test
    )
Select RecordId, PersonId
    , Case 
        When Seq = 1 Then 'Initial Record'
        Else 'Follow up Record'
        End As RecordStatus
From Data
Where SentDate Is Null