或者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
--1. 建一個結構與目標表一樣的臨時表 --select * into 臨時表 from 目標表 where 1<>1 --2. 通過下面的操作把目標表的想要資料保存到臨時表DECLARE Cursor_aa CURSOR FOR SELECT distinct col1 ,col2 FROM 目標表 OPEN Cursor_aa
declare @str1 varchar(10) declare @str2 varchar(10) FETCH NEXT FROM Cursor_aa Into @str1,@str2WHILE @@FETCH_STATUS = 0 BEGIN insert into 臨時表 select top 1 * from 目標表 where col1=@str1 and col2=@str2 FETCH NEXT FROM Cursor_aa Into @str1,@str2 END CLOSE Cursor_aa DEALLOCATE Cursor_aa--3. 刪除目標表 --4. select * into 目標表 from 臨時表 where 1=1
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
--select * into 臨時表 from 目標表 where 1<>1 --2. 通過下面的操作把目標表的想要資料保存到臨時表DECLARE Cursor_aa CURSOR FOR SELECT distinct col1 ,col2 FROM 目標表 OPEN Cursor_aa
declare @str1 varchar(10)
declare @str2 varchar(10)
FETCH NEXT FROM Cursor_aa Into @str1,@str2WHILE @@FETCH_STATUS = 0
BEGIN
insert into 臨時表 select top 1 * from 目標表 where col1=@str1 and col2=@str2
FETCH NEXT FROM Cursor_aa Into @str1,@str2
END CLOSE Cursor_aa
DEALLOCATE Cursor_aa--3. 刪除目標表
--4. select * into 目標表 from 臨時表 where 1=1