请一个求最大数的sql
table 1
id
name
table 2
id
UserName
table1Id

table 1
1 name1
2 name2

table 2
1 user1 1
2 user2 1
3 user3 2
我希望的数据
2 user2 1
3 user3 2

也就是有相同的table1Id的取tableid2大的那个记录

谢谢


[解决办法]

SQL code

CREATE TABLE TABLE2(id int,UserName nvarchar(20),table1Id int)INSERT INTO TABLE2 SELECT 1,'user1',1 UNION ALL                    SELECT 2,'user2',1 UNION ALL                    SELECT 3,'user3',2                        SELECT  [t3].[id] AS [Id], [t3].[UserName], [t3].[table1Id] AS [Table1Id]FROM (    SELECT [t0].[table1Id]    FROM [TABLE2] AS [t0]    GROUP BY [t0].[table1Id]    ) AS [t1]OUTER APPLY (    SELECT TOP (1) 1 AS [test], [t2].[id], [t2].[UserName], [t2].[table1Id]    FROM [TABLE2] AS [t2]    WHERE (([t1].[table1Id] IS NULL) AND ([t2].[table1Id] IS NULL)) OR (([t1].[table1Id] IS NOT NULL) AND ([t2].[table1Id] IS NOT NULL) AND ([t1].[table1Id] = [t2].[table1Id]))    ORDER BY [t2].[id] DESC    ) AS [t3]ORDER BY [t3].[id] DROP TABLE TABLE2Id          UserName             Table1Id----------- -------------------- -----------2           user2                13           user3                2
[解决办法]
SQL code
select * from tb2 where id in(select distinct MAX(id) id from tb2 group by table1Id)