我试图根据SSN后四个，姓氏和DOB来确定数据库中潜在的重复客户。我编写的存储过程确实会识别潜在的重复项，但它会将它们列出在一行中- 1 o% N& R( p) ]* }! O/ w+ Y5 f由于报告的原因，我试图把它分成几行。% U; o' l, R4 ]# G) ~! D
我的T-SQL看起来像：3 J- P$ p" g7 R2 V( D' S( b
DECLARE@StartDate DATE = @EndDate DATE = 3/1/2017；SELECT DENSE_RANK() OVER (ORDER BY c.socialSecurityNumber) AS [SSNRanking] , ROW_NUMBER() OVER (PARTITION BY c.socialSecurityNumber ORDER BY c.socialSecurityNumber) AS [RowNumb] , c.socialSecurityNumber AS [SSN], c.id AS [CustomerID] ， c.firstName AS [FirstName] ， c.lastName AS [lastName] , c.birthDate [birthdate] , c.createDate AS [CreateDate] , c2.socialSecurityNumber AS [DupSSN] , c2.id AS [DupCustomerID] , c2.firstName AS [DupFirstName] , c2.lastName AS [DupLastName] , c2.birthDate AS [DupBirthDate] , c2.createDate AS [DupCreateDate]FROM dbo.Customers AS [c] INNER JOIN dbo.Customers AS [c2] ON ( SUBSTRING(c.socialSecurityNumber,6,4) = SUBSTRING(c2.socialSecurityNumber,6,4) AND c.birthDate = c2.birthDate AND c.lastName = c2.lastName AND c.id c2.id ) LEFT JOIN dbo.CustomerAddresses AS [CA] ON c.id = CA.customerID LEFT OUTER JOIN dbo.Common_Orders AS [co] ON co.customerID = c.idWHERE c.customerStatusTypeID 'M' AND C2.customerStatusTypeID 'M' AND c.mergedTo IS NULL AND c2.mergedTo IS NULL AND CAST(co.orderDate AS DATE) >= @StartDate AND CAST(co.orderDate AS DATE) 我的数据集如下：: w7 n5 }8 g0 E
SSNRanking RowNumb SSN CustomerID FirstName lastName birthdate CreateDate DupSSN DupCustomerID DupFirstName DupLastName DupBirthDate DupCreateDate GREG GARRETT 1900-01-01 2014-02- ADAM GARRETT -01-01 2013-08-29在这个具体的例子中，我有两个用户有相同的用户SSN姓氏4，姓氏相同，姓氏相同DOB-但名字不同。 6 }) I: j' _* T如何在单独的行上显示这两个记录？理想情况下，我希望看到：! b2 R+ f& V1 C/ m% a/ S
SSNRanking RowNumb SSN CustomerID FirstName lastName birthdate CreateDate GREG GARRETT 1900-01-01 2014-02- 251 16084 ADAM GARRETT 1900-01-01 2013-08-29但是我不确定加同一张表怎么做。有什么建议吗？9 v* b' s# j' G- r6 Z7 e
我链接到一个脚本，创建两个有问题的表，并插入示例数据。希望这是可以接受的:SQL脚本8 B& g6 |* f( w% M) W- d' O
技术问答
207 人阅读
|
0 人回复
|
2023-09-12
