34,588
社区成员
发帖
与我相关
我的任务
分享
delete from ... where not exists ...
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