SQL if Record equals the record before it

问题:

I am trying to replicate a formula in excel into SQL, i have a list of customer ID codes with addresses.

I have concatenated the postcode and Surname and sorted them in ascending order.

I want to check if the the text string before equals the current one.

If it does then i want to put the ID number of the one before in a new field called New ID(on the current Record), and the old code in a new field called Deleted codes.

Basically we have duplicated addresses that we want to merge into one customer but without removing any data.

New code formula =IF(M20=M19,O19,A20) Deleted Code Formula =IF(N20=O20,"",N20)

Is this possible to do in SQL?

回答1:

;WITH MyCTE AS
(
    SELECT *, 
           ROW_NUMBER()OVER (ORDER BY TextString) AS rn
    FROM   Table1
)
SELECT T1.TextString AS T1String,
       T2.TextString AS T2String,
       CASE WHEN T1.TextString = T2.TextString THEN T1.ID ELSE '' END AS NewCode,
       CASE WHEN T1.TextString = T2.TextString THEN T2.ID ELSE '' END AS OldCode
FROM   MyCTE T1
       LEFT JOIN MyCTE T2
           ON T1.rn = T2.rn+1

SQL Fiddle Demo