問題描述
好的，我剛剛從我之前的問題中意識到這一點:根據輔助表中的行更新主表
Ok I just realized that from my previous question: Update main table based on rows from a secondary table
那是如何使用輔助表更新主表的計數，但由于此查詢將在 sql 作業中，我如何刪除我剛剛計算的行，并確保我不刪除任何新的行插入輔助表?
That was how to update the main table with the count using the secondary table, but since this query will be in a sql job, how can I delete the rows I just counted, and making sure I don't delete any new rows inserted into the secondary table?
推薦答案
如果我沒看錯，您想(在您的第一個問題中)使用與每個 EmployeeID 關聯的唯一 IP 數更新 SelectionCount，現在在同一過程中，從 Selection 表中清除構成這些計數的記錄 - 同時不觸及自 SelectionCount 更新以來添加的任何記錄.如果您的選擇表具有 1) 主鍵和 2) 行的創建日期，這將容易得多.兩者的缺失使事情變得復雜.
If I'm reading it right, you want to (in your first question) update the SelectionCount with the number of unique IPs associated with each EmployeeID, and now in the same process clear out the records from the Selection table that made up those count - while simultaneously not touching any records added since the SelectionCount was updated. This would be a lot easier if your selection table had 1) a primary key, and 2) a date the row was created. The absence of both complicates things.
因此，我們將采用一種稍微奇怪的方式(假設您無法更改架構).這假設您使用的是 SQL 2005 或 2008 :
So we'll do this a slightly odd way (assuming you're not able to change your schema). This assumes you're using SQL 2005 or 2008 :
我在這里猜測你的數據類型
I'm guessing your datatypes here
DECLARE @TempIPs TABLE(EmployeeID int, ipaddress varchar(25))
DELETE FROM Selection
OUTPUT deleted.EmployeeID, deleted.ipAddress
INTO @TempIPs
--用保存舊 IP 的 @TempIPs 替換對選擇表的引用.
--replace reference to Selection table with @TempIPs which holds the legacy IPs.
UPDATE Employee
SET SelectionCount = IsNull((SELECT Count(DISTINCT ipAddress)
FROM @TempIPs
WHERE @TempIPs.EmployeeID = Employee.ID), 0)
如果您計劃在添加新記錄時繼續增加 SelectionCount，只需將 + SelectionCount 添加到您的 UPDATE 語句中.您還應該將整個批次包裝在交易 (BEGIN TRANSACTION....COMMIT TRANSACTION) 語句中.
If you're planning to keep increasing the SelectionCount as new records are added, just add + SelectionCount to your UPDATE statement. You should also wrap the whole lot inside a transaction (BEGIN TRANSACTION....COMMIT TRANSACTION) statement.
這篇關于如何將計數的行標記為已刪除?續上一個問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！