表中四个字段,求一个SQL语句,删除前三个字段值相同且最后一个字段不是相同记录中最大值的记录。
例如:
字段1 字段2 字段3 字段4
1 2 3 1
1 2 3 2
1 2 3 32 3 3 1
2 3 3 2
2 3 3 3
最后保留的记录是
1 2 3 3
2 3 3 3
例如:
字段1 字段2 字段3 字段4
1 2 3 1
1 2 3 2
1 2 3 32 3 3 1
2 3 3 2
2 3 3 3
最后保留的记录是
1 2 3 3
2 3 3 3
go
create table [tb]([字段1] int,[字段2] int,[字段3] int,[字段4] int)
insert [tb]
select 1,2,3,1 union all
select 1,2,3,2 union all
select 1,2,3,3 union all
select 2,3,3,1 union all
select 2,3,3,2 union all
select 2,3,3,3select * from [tb]delete a
from tb a
where exists(select 1 from tb where [字段1]=a.[字段1] and [字段2]=a.[字段2] and [字段3]=a.[字段3] and [字段4]>a.[字段4])
select * from [tb]
--测试结果:
/*
1 2 3 3
2 3 3 3
*/
where not exists(select 1 from ta b where ta.col1 = b.col1 and ta.col2=b.col2 and ta.col3=b.col3 and ta.col4>b.col4)
where exists(
select *
from table
where 字段1 = a.字段1
and 字段2 = a.字段2
and 字段3 = a.字段3
and 字段4 < a.字段4
)
--写反了
delete a
from table a
where exists(
select *
from table
where 字段1 = a.字段1
and 字段2 = a.字段2
and 字段3 = a.字段3
and 字段4 > a.字段4
)
(col1 int,
col2 int,
col3 int,
col4 int)
insert into ta
select 1,2,3,1 union all
select 1,2,3,2 union all
select 1,2,3,3 union all
select 2,3,3,1 union all
select 2,3,3,2 union all
select 2,3,3,3
delete from ta
where exists(select 1 from ta b where ta.col1 = b.col1 and ta.col2=b.col2 and ta.col3=b.col3 and ta.col4<b.col4)
select * from [ta]
drop table [ta]
-- Author: liangCK 小梁
-- Date : 2008-11-08 15:14:41
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (字段1 INT,字段2 INT,字段3 INT,字段4 INT)
INSERT INTO @T
SELECT 1,2,3,1 UNION ALL
SELECT 1,2,3,2 UNION ALL
SELECT 1,2,3,3 UNION ALL
SELECT 2,3,3,1 UNION ALL
SELECT 2,3,3,2 UNION ALL
SELECT 2,3,3,3--SQL查询如下:DELETE A
FROM @T AS A
LEFT JOIN
(
SELECT 字段1,字段2,字段3,MAX(字段4) AS 字段4
FROM @T
GROUP BY 字段1,字段2,字段3
) AS B
ON A.字段1=B.字段1
AND A.字段2=B.字段2
AND A.字段3=B.字段3
AND A.字段4=B.字段4
WHERE B.字段1 IS NULL
AND B.字段2 IS NULL
AND B.字段3 IS NULL
AND B.字段4 IS NULL
SELECT * FROM @T/*
字段1 字段2 字段3 字段4
----------- ----------- ----------- -----------
1 2 3 3
2 3 3 3(2 行受影响)
*/
create table t1(a char(10),b char(10),c char(10),d char(10))
insert into t1 values('1','2','3','1')
insert into t1 values('1','2','3','2')
insert into t1 values('1','2','3','3')
insert into t1 values('2','3','3','1')
insert into t1 values('2','3','3','2')
insert into t1 values('2','3','3','3')
go
----------按表取d最大的值所在行的数据。
select * from t1 where d=(select max(d) from t1 where a=t1.a and b=t1.b and c=t1.c )