日期:2014-05-18 浏览次数:20521 次
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'GUARANTEE') BEGIN DROP TABLE GUARANTEE END GO CREATE TABLE GUARANTEE ( PRODUCT_TYPE INT, MODELNAME VARCHAR(100), AFFIXNAME VARCHAR(100), AFFIXSNAME VARCHAR(100), FIXDATE VARCHAR(10), FIXMAN VARCHAR(10), AFFIXID INT ) INSERT INTO GUARANTEE SELECT 1,'','语音盒','HD1600BBQ','2012-01-01','安装老大',2 UNION SELECT 0,'龙翰16','','','2011-12-08','测试老大',NULL UNION SELECT 0,'长江S300','','','2011-12-01','测试老大',NULL UNION SELECT 1,'','油量检测','','2011-12-01','测试老四',3 UNION SELECT 1,'','卸料检测','HD1702XLS','2011-12-01','我饿uife',5 SELECT * FROM GUARANTEE AS T WHERE (SELECT COUNT(*) FROM GUARANTEE WHERE ISNULL(t.AFFIXID,0) = ISNULL(AFFIXID,0) AND FIXDATE > T.FIXDATE) < 1 PRODUCT_TYPE MODELNAME AFFIXNAME AFFIXSNAME FIXDATE FIXMAN AFFIXID 0 龙翰16 2011-12-08 测试老大 NULL 1 卸料检测 HD1702XLS 2011-12-01 我饿uife 5 1 油量检测 2011-12-01 测试老四 3 1 语音盒 HD1600BBQ 2012-01-01 安装老大 2
------解决方案--------------------
去重就是删除多余的数据,但前提是你找出正确的数据来。然后:
delete from ... where not exists ...