40
查詢數據表裏所有重複裏的單條記錄
測試腳本：CREATE TABLE [dbo].[TEST]( [ID] [int] IDENTITY(1,1) NOT NULL, [TID] [int] NULL, [Discription] [nvarchar](200) NULL ) --測試數據 DELETE FROM TEST INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-1') INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-2') INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-3') INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-4') INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-5') INSERT INTO TEST (TID,Discription)VALUES (2,'記錄2-1') INSERT INTO TEST (TID,Discription)VALUES (2,'記錄2-2') INSERT INTO TEST (TID,Discription)VALUES (2,'記錄2-3') SELECT * FROM TEST查詢結果：
TID為重複字段，現在需找出所有TID中ID字段值最大的記錄。
如下圖所示：
解決辦法：GROUP BY() 函數+Max()函數
SELECT * FROM TEST WHERE ID IN (SELECT MAX(ID)FROM Test GROUP BY TID )
最後更新：2017-04-02 06:52:21