在資料庫操作時, 有時會需要比對資料表的內容是否一致, 或是某些資料的某些欄位是否一樣, 若是使用 SQL 指令來逐行比對, 會是個比較麻煩的操作方式, 使用 CHECKSUM 函數, 可以方便地將待比對的資料(多欄亦可), 計算出 hash index (int)後, 再進行比對, 舉例如下:
兩個 table 資料:
table TBL_TMP1
fno fdata fcreatetime
1 ‘data 1’ ‘2012/1/1’
2 ‘data 2’ ‘2012/2/3’
3 ‘data 3’ ‘2012/2/4’
4 ‘data 4’ ‘2012/4/1’
table TBL_TMP2
fno fdata fcreatetime
1 ‘data 001’ ‘2012/1/1’
2 ‘data 2’ ‘2012/2/12’
3 ‘data 3’ ‘2012/2/4’
4 ‘data 4’ ‘2012/4/1’
若要找出資料不同的資料列, 可以使用如下指令:
SELECT * FROM (SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP1) a INNER JOIN (SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP2) b ON a.fno = b.fno AND a.CHK != b.CHK
其中用了 CHECKSUM(*) 是將 table 中的各欄位合併起來計算 hash index, 再將兩個 table 含有 hash index (欄位名 CHK)拿來比對, 列出不同的資料, 結果如下:
1 data 1 2012-01-01 00:00:00.000 803471792 1 data 001 2012-01-01 00:00:00.000 1877235137
2 data 2 2012-02-03 00:00:00.000 266601110 2 data 2 2012-02-12 00:00:00.000 266601103
是很方便的工具函數.
附上建立上面 sample code 的 create table 及 insert data 指令:
CREATE TABLE TBL_TMP2 (fno int, fdata varchar(20), fcreatetime datetime) INSERT INTO TBL_TMP1 values (1, 'data 1', '2012/1/1') INSERT INTO TBL_TMP1 values (2, 'data 2', '2012/2/3') INSERT INTO TBL_TMP1 values (3, 'data 3', '2012/2/4') INSERT INTO TBL_TMP1 values (4, 'data 4', '2012/4/1') INSERT INTO TBL_TMP2 values (1, 'data 001', '2012/1/1') INSERT INTO TBL_TMP2 values (2, 'data 2', '2012/2/12') INSERT INTO TBL_TMP2 values (3, 'data 3', '2012/2/4') INSERT INTO TBL_TMP2 values (4, 'data 4', '2012/4/1')
大家可以試看看, 若是只要比對前兩欄, 則指令如下:
SELECT * FROM (SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP1) a INNER JOIN (SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP2) b ON a.fno = b.fno AND a.CHK != b.CHK
結果如下, 就是只有第一筆資料的前兩欄不同而已:
1 data 1 2012-01-01 00:00:00.000 -1291957785 1 data 001 2012-01-01 00:00:00.000 -1493283680
如同我們想要的結果一樣. 這樣一來可以方便活用這個 CHECKSUM 函數, 十分方便好用!
相關資料:
http://msdn.microsoft.com/en-us/library/ms189788.aspx
資料上寫到是 SQL Server 2005 以上版本可用, 不過其實在 SQL Server 2000 也有這個指令:
http://msdn.microsoft.com/en-us/library/aa258245%28v=sql.80%29.aspx