对于表中某一字段x,如果有x相同的记录 只留下一条,其余的删除。
x的内容不定,相同的记录数不定。
比如,按第一个字段:
A 1 2 3
A 3 4 5
A 6 7 8
B 1 2 4
B 2 2 1
C 2 2 2
可能的输出是:
A 1 2 3
B 1 2 4
C 2 2 2
x的内容不定,相同的记录数不定。
比如,按第一个字段:
A 1 2 3
A 3 4 5
A 6 7 8
B 1 2 4
B 2 2 1
C 2 2 2
可能的输出是:
A 1 2 3
B 1 2 4
C 2 2 2
比如:
A 1 2 3
A 3 4 5
A 6 7 8
B 1 2 4
B 2 2 1
C 2 2 2
字段一相同的留下字段二值小的记录?
where exists( select * from 表 where x=a.x and 主键>a.主键)
Insert T1 Select 'A',1,2,3
Union all Select 'A',3,4,5
Union all Select 'A',6,7,8
Union all Select 'B',1,2,4
Union all Select 'B',2,2,1
Union all Select 'C',2,2,2
--
--借用临时表
Select *, Identity(Int,1,1) As Id Into # From T1
--删除重复记录
Delete A From # A
Where Exists
(Select 1 From # Where F1=A.F1 And Id<A.ID )
--删除后的结果
Select F1,F2,F3,F4 From #
(col1 char(1),
col2 int,
col3 int,
col4 int)insert #test(col1,col2,col3,col4)
SELECT 'A',1,2,3 UNION ALL
SELECT 'A',3,4,5 UNION ALL
SELECT 'A',6,7,8 UNION ALL
SELECT 'B',1,2,4 UNION ALL
SELECT 'B',2,2,1 UNION ALL
SELECT 'C',2,2,2SELECT * FROM #TEST A
WHERE NOT EXISTS (SELECT 1 FROM #TEST WHERE COL1=A.COL1 AND COL2<A.COL2)select * from #testdrop table #test
(col1 char(1),
col2 int,
col3 int,
col4 int)insert #test(col1,col2,col3,col4)
SELECT 'A',1,2,3 UNION ALL
SELECT 'A',1,2,3 UNION ALL
SELECT 'A',3,4,5 UNION ALL
SELECT 'A',6,7,8 UNION ALL
SELECT 'B',1,2,4 UNION ALL
SELECT 'B',2,2,1 UNION ALL
SELECT 'C',2,2,2SELECT id=identity(int,1,1), * into # FROM #TEST SELECT COL1,COL2,COL3,COL4 from # A
WHERE NOT EXISTS (SELECT 1 FROM # WHERE COL1=A.COL1 AND ID<A.ID)--select * from #testdrop table #test,#
godelete a from 你的表 a
where exists(
select * from 你的表 where x=a.x and del_>a.del_)
goalter table 你的表 drop column del_
Delete A From # A 与 Delete From # A 有什么不同???
本人初学SQL请问:
Delete A From # A 与 Delete From # A 有什么不同???
--------------------------------------
Delete From # A這個會報錯。可以這樣
Delete From #區別在於一個用了別名,一個沒用。