重复数据删除

Question

下面的代码最好地解释了我想要完成的任务。我知道我可以使用游标或其他循环例程来循环记录以查找重复项，并根据找到的内容创建我的笔记记录。我会尽力避免这种情况，除非没有更好的选择。

DROP TABLE #orig
DROP TABLE #parts
DROP TABLE #part_notes

CREATE TABLE #orig(partnum VARCHAR(20), notes VARCHAR(100));
INSERT INTO #orig VALUES ('A123', 'To be used on Hyster models only')
INSERT INTO #orig VALUES ('A123', 'Right Hand model only')
INSERT INTO #orig VALUES ('A125', 'Not to be used by Jerry')
INSERT INTO #orig VALUES ('A125', NULL)
INSERT INTO #orig VALUES ('A125', 'asdfasdlfj;lsdf')
INSERT INTO #orig VALUES ('A128', 'David test')
INSERT INTO #orig VALUES ('A129', 'Fake part')

SELECT COUNT(*) FROM #orig

-- SHOW ME UNIQUE PARTS, MY PARTS TABLE SHOULD BE UNIQUE!
SELECT DISTINCT partnum FROM #orig


CREATE TABLE #parts(id INT IDENTITY(1,1), partnum VARCHAR(20));
INSERT INTO #parts
SELECT DISTINCT partnum FROM #orig

SELECT * FROM #parts

CREATE TABLE #part_notes(id INT IDENTITY(1,1), part_id INT, line_number INT, notes VARCHAR(100));
/*
    HOW DO I AT THIS POINT POPULATE the #part_notes table so that it looks like this:
    (note: any NULL or empty note strings should be ignored)

    id  part_id line_number notes
    1   1       1           To be used on Hyster models only    
    2   1       2           Right Hand model only
    3   2       1           Not to be used by Jerry
    4   2       2           asdfasdlfj;lsdf
    6   3       1           David test
    7   4       1           Fake part

*/

Answer 1

下面只是任意选择 line_numbers，因为数据中似乎没有任何适合 order by 的内容。

SELECT   p.id part_id,
         p.partnum   ,
         ROW_NUMBER() over (partition BY p.id ORDER BY (SELECT 0)) line_number,
         notes
FROM     #parts p
         JOIN #orig o
         ON       o.partnum=p.partnum
WHERE    notes   IS NOT NULL
AND      notes            <> ''
ORDER BY part_id