架设有如下数据:
DECLARE @tb TABLE (col1 INT,col2 VARCHAR(100))
INSERT INTO @tb VALUES (10,'a'), (11,'b'), (10,'a'), (10,'a'), (10,'a'), (10,'a'), (10,'a'), (11,'b')
SELECT * FROM @tb
col1 | col2 |
---|---|
10 | a |
11 | b |
10 | a |
10 | a |
10 | a |
10 | a |
10 | a |
11 | b |
如果想更新或删除上面数据中的某些行,那么无法直接用Where指定条件。
可以将数据临时增加一个需要列再处理。
其实不用额外处理数据,利用SQL 内部数据存储的物理地址也可以定位某行数据。
在SQL Server 2005里可以通过%%lockres%%得到数据行对应的物理路径信息,在SQL Server2008里还可以通过%%physloc%% 得到,用sys.fn_PhysLocFormatter 将得到的binary数据转换后就是%%lockres%% 的值。
注意上面的是 Undocumented Function,使用有风险,sys.fn_PhysLocFormatter还有个bug
SELECT %%Physloc%% AS Addr,sys.fn_PhysLocFormatter(%%Physloc%%) AS rowid , * FROM @tb
Addr | rowid | col1 | col2 |
---|---|---|---|
0x2008000003000000 | (3:2080:0) | 10 | a |
0x2008000003000100 | (3:2080:1) | 11 | b |
0x2008000003000200 | (3:2080:2) | 10 | a |
0x2008000003000300 | (3:2080:3) | 10 | a |
0x2008000003000400 | (3:2080:4) | 10 | a |
0x2008000003000500 | (3:2080:5) | 10 | a |
0x2008000003000600 | (3:2080:6) | 10 | a |
0x2008000003000700 | (3:2080:7) | 11 | b |
假设现在要求重复的数据仅留一行,那么就可以利用地址来标识。
按如下执行后将仅余两行数据
DELETE t1 FROM @tb AS t1
INNER JOIN (SELECT col1,col2,MAX(%%Physloc%%) as maxIndex FROM @tb GROUP BY col1,col2) t2 ON t1.col1=t2.col1 AND t1.col2=t2.col2
WHERE (t1.%%Physloc%%)!=t2.maxIndex
执行后:
SELECT * FROM @tb
col1 | col2 |
---|---|
10 | a |
11 | b |