--删除重复记录 DECLARE @ TABLE(id INT IDENTITY, sName VARCHAR(10) ) INSERT INTO @ SELECT '張三' UNION ALL SELECT '王二' UNION ALL SELECT '張三' UNION ALL SELECT '李四' UNION ALL SELECT '王二'DELETE FROM @ WHERE NOT id IN (SELECT MAX(id) FROM @ GROUP BY sName) SELECT * FROM @
自己解决了DELETE FROM useinfo WHERE (new_id NOT IN (SELECT MIN(new_id) AS id FROM useinfo GROUP BY id)) 删除重复记录增加自增字段 ALTER TABLE useinfo ADD new_id Int IDENTITY (1, 1)删除字段 ALTER TABLE useinfo DROP COLUMN testDELETE FROM bid_shenhe WHERE (new_id NOT IN(SELECT MIN(new_id) AS id FROM bid_shenhe GROUP BY bsh_id))
不是阿,我的表格:test viceid content tel 1 weiekieeiirr 6789765 2 ddddddddd 555555 2 ddddddddd 555555 2 ddddddddd 555555 我想要删除viceid=2的两个,保留一个 使用delete from test where viceid not in(select viceid from test group bu viceid) 结果执行后没有什么变化 (所影响的行数为 0 行) 应该怎么办?
用SQL语句执行??
DECLARE @ TABLE(id INT IDENTITY, sName VARCHAR(10)
)
INSERT INTO @ SELECT '張三'
UNION ALL SELECT '王二'
UNION ALL SELECT '張三'
UNION ALL SELECT '李四'
UNION ALL SELECT '王二'DELETE FROM @ WHERE NOT id IN (SELECT MAX(id) FROM @ GROUP BY sName)
SELECT * FROM @
WHERE (new_id NOT IN
(SELECT MIN(new_id) AS id
FROM useinfo
GROUP BY id)) 删除重复记录增加自增字段
ALTER TABLE useinfo ADD new_id Int IDENTITY (1, 1)删除字段
ALTER TABLE useinfo DROP COLUMN testDELETE FROM bid_shenhe WHERE (new_id NOT IN(SELECT MIN(new_id) AS id FROM bid_shenhe GROUP BY bsh_id))
viceid content tel
1 weiekieeiirr 6789765
2 ddddddddd 555555
2 ddddddddd 555555
2 ddddddddd 555555
我想要删除viceid=2的两个,保留一个
使用delete from test where viceid not in(select viceid from test group bu viceid)
结果执行后没有什么变化
(所影响的行数为 0 行)
应该怎么办?
http://www.cnblogs.com/piaoqingsong/archive/2006/08/08/470931.html