Trong bài đăng này, tôi sẽ cung cấp cho bạn một bản demo đầy đủ để tìm một số cột nhận dạng bị thiếu trong SQL Server.
Trước đó vài ngày, tôi đang làm báo cáo sản xuất và thấy rằng một số bị thiếu. Trình tự và số còn thiếu này rất quan trọng.
Ngay lập tức một yêu cầu đến bàn của tôi là, vui lòng tìm danh sách tất cả các số còn thiếu để phục vụ mục đích điều tra.
Dưới đây là tập lệnh demo đầy đủ để tìm số bị thiếu trong SQL Server.
Đầu tiên Tạo một bảng mẫu với dữ liệu.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | /*Create one table to store Numbers*/ CREATE TABLE dbo.tbl_Numbers ( ID INTEGER ) GO /*Insert some test records. You can also see I have missed many number which we are going to find by query*/ INSERT INTO dbo.tbl_Numbers VALUES(1),(2),(3),(4),(6),(7),(9),(10) ,(11),(15),(16),(19),(22),(23),(24) ,(28),(32),(33),(34),(35),(38),(40) ,(41),(42),(43),(44),(45),(47),(49) ,(50) GO |
Dưới đây là kịch bản từng bước để tìm số còn thiếu này.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | /*Step 1: Create one temp table and store all distinct Numbers*/ SELECT DISTINCT ID INTO #DistinctID FROM dbo.tbl_Numbers GO /*Step 2: Find NextID which has something missing before it. Store into one TempTable*/ SELECT (ROW_NUMBER()OVER (ORDER BY ID ASC)) AS TableCountID ,b.ID ,( SELECT Top 1 ID FROM #DistinctID a WITH (NOLOCK) WHERE a.ID > b.ID ) AS NextID INTO #NextID FROM #DistinctID AS b WITH (NOLOCK) WHERE ( SELECT Top 1 ID FROM #DistinctID a WITH (NOLOCK) WHERE a.ID > b.ID ) <> b.ID+1 ORDER BY b.ID /*Step 3: Create one temp table to store missing number details */ CREATE TABLE #MissingID ( TableCountID INTEGER ,ID INTEGER ,NextID INTEGER ,MissingID INTEGER ,TotalDiff INTEGER ) GO /*Step 4: Now start to find missing id using WHILE Loop. */ DECLARE @CountID INTEGER DECLARE @InnerLoopCountID INTEGER SET @CountID = 1 WHILE (@CountID > 0) BEGIN INSERT INTO #MissingID SELECT TableCountID ,ID ,NextID ,CASE WHEN TotalDiff = 1 THEN ID+TotalDiff END AS MissingID ,TotalDiff FROM ( SELECT TableCountID ,ID ,NextID ,(NextID-ID)-1 AS TotalDiff FROM #NextID ) AS T WHERE TableCountID = @CountID IF ((SELECT (NextID-ID)-1 FROM #NextID WHERE TableCountID = @CountID)>1) BEGIN SET @InnerLoopCountID = (SELECT (NextID-ID)-1 FROM #NextID WHERE TableCountID = @CountID) WHILE (@InnerLoopCountID > 0) BEGIN INSERT INTO #MissingID SELECT TableCountID ,ID ,NextID ,ID+@InnerLoopCountID AS MissingNumber ,TotalDiff FROM ( SELECT TableCountID ,ID ,NextID ,(NextID-ID)-1 AS TotalDiff FROM #NextID ) AS T WHERE TableCountID = @CountID SET @InnerLoopCountID = @InnerLoopCountID - 1 END END SET @CountID = @CountID+1 IF @CountID = (SELECT COUNT(1)+1 FROM #NextID) BEGIN RETURN END END GO /*Step 5: Now select missing data from #MissingID temp table. You can find full details on MissingID.*/ SELECT *FROM #MissingID WHERE MissingID IS NOT NULL ORDER BY MissingID GO |
Kết quả của Bước 5:
Trong kết quả trên, bạn có thể tìm thấy tổng số 20 số còn thiếu. Cột MissingID đại diện cho các số bị thiếu.