请一个求最大数的sql解决方案
日期：2014-05-18 浏览次数：20442 次
请一个求最大数的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大的那个记录
谢谢
------解决方案--------------------
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 TABLE2
Id UserName Table1Id
----------- -------------------- -----------
2 user2 1
3 user3 2
------解决方案--------------------
- SQL code
select * from tb2 where id in(select distinct MAX(id) id from tb2 group by table1Id)
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
