A304-農業水資源智慧決策支援平台

Step 1 尋找重覆的資料¶

語法：

select distinct BoundaryID, BoundaryType, DataTime, Rain, count(*) from tbl_GridBoundaryRainfallRealTime
group by BoundaryID, BoundaryType, DataTime, Rain
having count(*) >1
order by BoundaryID, BoundaryType, DataTime desc, Rain

Step 2 備份資料表¶

語法：

select * into tbl_GridBoundaryRainfallRealTime_0203 from tbl_GridBoundaryRainfallRealTime

Step 3 刪除重覆資料¶

語法：

;WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY BoundaryID, BoundaryType, DataTime, Rain ORDER BY (SELECT NULL)) AS rn
    FROM tbl_GridBoundaryRainfallRealTime
)
DELETE FROM CTE
WHERE rn > 1;