请一个求最大数的sql

[hebeijg]

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大的那个记录

谢谢

[EnForGrass]

那跟table1有什么关系呢

[q107770540]

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 TABLE2
Id          UserName             Table1Id
----------- -------------------- -----------
2           user2                1
3           user3                2

[lycorisraya]

select * from tb2 where id in(select distinct MAX(id) id from tb2 group by table1Id)

不知道你要的是不是这个结果

[冷玩笑]

[Quote=引用 3 楼 zxg1258 的回复:]

SQL code
select * from tb2 where id in(select distinct MAX(id) id from tb2 group by table1Id)

不知道你要的是不是这个结果
[/Quote]

这样就能满足楼主的需求

[gw6328]

select * from t2 where id in(
select max(id) from t2 where t1id in (select id from t1) group by t1id
)

[yibey]

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

这句话楼主描述错误了吧 应该是：也就是有相同的table1Id的取tableid2的ID最大的那个记录
而且这样看来的确跟TABLE1没有任何关系

select * from table2 a where not exists(select 1 from table2 where a.table1Id = table1Id and id>a.id )