我有三张表：
有时我知道学生转学到不同的学校，在这种情况下，我只需更换Student.SchoolID新的SchoolID.
通常我没有发现学生已经转学，在这种情况下，我最终会遇到两个名字、姓氏、出生日期相同但学校 ID 不同的学生。我将称这些重复学生（索引防止所有四个字段相同）。发生这种情况时，我必须去StudentTest表并更改StudentTest.StudentID以匹配新的StudentID. 在所有测试转移到“新”学生后，我可以删除旧的学生记录。
在清理包含 35,000 名学生的 SQL Server 数据库时，我复制了 1600 名学生，并与重复学生进行了不同数量的测试。
我可以选择重复学生的测试，以便将它们组合在一起，从视图 ( vwTestList，将一些学生信息与测试信息结合在一起) 中获取：
SELECT
a.SchoolID as SchID, a.StudentID as StudID, a.StudentTestID as TestID,
a.LastName as LName, a.FirstName as FName,
a.Birthdate as BDate, a.Testdate as Tdate, a.Grade
FROM
dbo.vwTestList a
JOIN
(SELECT
firstname, lastname, BirthDate, SchoolID
FROM
dbo.vwTestList
GROUP BY
firstname, lastname, BirthDate, SchoolID
HAVING
COUNT(*) > 1) b ON a.firstname = b.firstname
AND a.lastname = b.lastname
AND a.BirthDate = b.BirthDate
AND a.SchoolID <> b.SchoolID
ORDER BY
a.LastName, a.FirstName, a.BirthDate, a.Testdate DESC
示例结果：
SchID StudID TestID LName FName Bdate TDate Grade
----------------------------------------------------------------------
461 16172 142773 Auk Jay 2000-06-29 2010-04-13 4.7
461 16172 136350 Auk Jay 2000-06-29 2009-04-14 3.7
146 5234 128517 Auk Jay 2000-06-29 2008-04-01 2.7
146 5234 123560 Auk Jay 2000-06-29 2007-04-10 1.7
但是，我无法找出将每组重复学生的所有测试更改为该组中StudentID最新测试的更新查询。在这个例子中，Jay Auk 的所有测试都应该StudentID以 16172结束。任何帮助将不胜感激！
查看以下示例，看看这是否有助于您入门。此示例可以在 SSMS 中运行。
-- replicate environment --
DECLARE @TestView TABLE (
SchID INT, StudID INT, TestID INT, LName VARCHAR(30), FName VARCHAR(30), BDate DATETIME, TDate DATETIME, Grade DECIMAL(10,1)
)
INSERT INTO @TestView (
SchID, StudID, TestID, LName, FName, BDate, TDate, Grade
)
VALUES
( 461, 16172, 142773, 'Auk', 'Jay', '2000-06-29', '2010-04-13', 4.7 )
, ( 461, 16172, 136350, 'Auk', 'Jay', '2000-06-29', '2009-04-14', 3.7 )
, ( 146, 5234, 128517, 'Auk', 'Jay', '2000-06-29', '2008-04-01', 2.7 )
, ( 146, 5234, 123560, 'Auk', 'Jay', '2000-06-29', '2007-04-10', 1.7 )
, ( 152, 17899, 123561, 'Gates', 'Bill', '1955-10-28', '2007-04-15', 4.7 )
, ( 152, 17899, 123562, 'Gates', 'Bill', '1955-10-28', '2007-04-14', 3.7 )
, ( 157, 5235, 123563, 'Gates', 'Bill', '1955-10-28', '2007-04-01', 2.7 )
, ( 157, 5235, 123564, 'Gates', 'Bill', '1955-10-28', '2007-04-10', 1.7 );
-- starting data --
SELECT * FROM @TestView;
返回：
+-------+--------+--------+-------+-------+-------------------------+-------------------------+-------+
| SchID | StudID | TestID | LName | FName | BDate | TDate | Grade |
+-------+--------+--------+-------+-------+-------------------------+-------------------------+-------+
| 461 | 16172 | 142773 | Auk | Jay | 2000-06-29 00:00:00.000 | 2010-04-13 00:00:00.000 | 4.7 |
| 461 | 16172 | 136350 | Auk | Jay | 2000-06-29 00:00:00.000 | 2009-04-14 00:00:00.000 | 3.7 |
| 146 | 5234 | 128517 | Auk | Jay | 2000-06-29 00:00:00.000 | 2008-04-01 00:00:00.000 | 2.7 |
| 146 | 5234 | 123560 | Auk | Jay | 2000-06-29 00:00:00.000 | 2007-04-10 00:00:00.000 | 1.7 |
| 152 | 17899 | 123561 | Gates | Bill | 1955-10-28 00:00:00.000 | 2007-04-15 00:00:00.000 | 4.7 |
| 152 | 17899 | 123562 | Gates | Bill | 1955-10-28 00:00:00.000 | 2007-04-14 00:00:00.000 | 3.7 |
| 157 | 5235 | 123563 | Gates | Bill | 1955-10-28 00:00:00.000 | 2007-04-01 00:00:00.000 | 2.7 |
| 157 | 5235 | 123564 | Gates | Bill | 1955-10-28 00:00:00.000 | 2007-04-10 00:00:00.000 | 1.7 |
+-------+--------+--------+-------+-------+-------------------------+-------------------------+-------+
您可以在上面看到起始值。
继续...
-- update the StudID to the most recent test StudID for student --
UPDATE @TestView
SET
StudID = Tests.MostRecentID
FROM @TestView vwTestList
CROSS APPLY (
SELECT TOP 1 StudID AS MostRecentID FROM @TestView vw
WHERE
vw.LName = vwTestList.LName
AND vw.FName = vwTestList.FName
ORDER BY vw.TDate DESC
) AS Tests;
-- view results --
SELECT * FROM @TestView;
返回：
+-------+--------+--------+-------+-------+-------------------------+-------------------------+-------+
| SchID | StudID | TestID | LName | FName | BDate | TDate | Grade |
+-------+--------+--------+-------+-------+-------------------------+-------------------------+-------+
| 461 | 16172 | 142773 | Auk | Jay | 2000-06-29 00:00:00.000 | 2010-04-13 00:00:00.000 | 4.7 |
| 461 | 16172 | 136350 | Auk | Jay | 2000-06-29 00:00:00.000 | 2009-04-14 00:00:00.000 | 3.7 |
| 146 | 16172 | 128517 | Auk | Jay | 2000-06-29 00:00:00.000 | 2008-04-01 00:00:00.000 | 2.7 |
| 146 | 16172 | 123560 | Auk | Jay | 2000-06-29 00:00:00.000 | 2007-04-10 00:00:00.000 | 1.7 |
| 152 | 17899 | 123561 | Gates | Bill | 1955-10-28 00:00:00.000 | 2007-04-15 00:00:00.000 | 4.7 |
| 152 | 17899 | 123562 | Gates | Bill | 1955-10-28 00:00:00.000 | 2007-04-14 00:00:00.000 | 3.7 |
| 157 | 17899 | 123563 | Gates | Bill | 1955-10-28 00:00:00.000 | 2007-04-01 00:00:00.000 | 2.7 |
| 157 | 17899 | 123564 | Gates | Bill | 1955-10-28 00:00:00.000 | 2007-04-10 00:00:00.000 | 1.7 |
+-------+--------+--------+-------+-------+-------------------------+-------------------------+-------+
StudID 已更新为每行的最新测试 StudID。加入名字让我很紧张，但如果你没有另一种独特的方式来保证学生匹配（例如两个学生有相同的名字），那么可能没有很多其他选择。
与往常一样，在实际运行更新之前验证您的数据，但这应该可以帮助您入门。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句