测试脚本:
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 )