求救:在线急等,谢谢,求过滤重复数据的SQL语句
我有一个表;
有好多列,其中有两个列可能会重复,我想从重复的数据中只留下任意一条数据
col1 col2 col3
1 a m
1 a n
2 a i
2 a r
1 b h
1 b d
3 b o
想得到结果:
col1 col2 col3
1 a m
2 a i
1 b h
3 b o
[解决办法]
Select col1, col2, Min(col3) As col3 From 表 Group By col1, col2
[解决办法]
或者
Select col1, col2, Max(col3) As col3 From 表 Group By col1, col2
[解决办法]
select col1,col2,min(col3) as col3 from 表 group by col1,col2
[解决办法]
--方法一
Select * From 表 A Where Not Exists(Select col3 From 表 Where col1 = A.col1 And col2 = A.col2 And col3 > A.col3)
--方法二
Select * From 表 A Where col3 = (Select Max(col3) From 表 Where col1 = A.col1 And col2 = A.col2)
--方法三
Select A.* From 表 A
Inner Join
(Select col1, col2, Max(col3) As col3 From 表 Group By col1, col2) B
On A.col1 = B.col1 And A.col2 = B.col2 And A.col3 = B.col3
